Subversion Repositories SmartDukaan

-- Partner investment snapshot.
--
-- One row per active franchise partner, refreshed by the 2-minute sweep
-- (PartnerInvestmentSweepService). Replaces the 3-hour Redis cache that used to sit in
-- front of PartnerInvestmentServiceImpl.getInvestment().
--
-- Every column in a row is computed in ONE pass, so coupled terms (wallet vs utilised,
-- in-stock vs aged-Apple) can never be read at different vintages. That skew is what
-- caused a partner's credit limit to be overstated by the full value of an advance
-- payment for an hour on 2026-09-10.
--
-- Money is DECIMAL, not FLOAT: float money columns lose paise above ~1.67 lakh, and
-- base_value is compared on every sweep to decide whether the limit must be recomputed.
--
-- Safe to re-run.

CREATE TABLE IF NOT EXISTS fofo.partner_investment (
    fofo_id                      INT            NOT NULL,

    -- refreshed every 2 minutes
    wallet_amount                DECIMAL(14,2)  NOT NULL DEFAULT 0,

    -- GROSS stock. Investment counts stock net of live-demo units held past 90 days, but that
    -- haircut moves on its own daily clock, so storing the netted figure would mean re-netting it
    -- every time the haircut changed. Store what is measured and derive the net
    -- (getNetInStockAmount = gross - demo_excluded_amount).
    in_stock_gross_amount        DECIMAL(14,2)  NOT NULL DEFAULT 0,
    unbilled_amount              DECIMAL(14,2)  NOT NULL DEFAULT 0,
    grn_pending_amount           DECIMAL(14,2)  NOT NULL DEFAULT 0,
    activated_stock_amount       DECIMAL(14,2)  NOT NULL DEFAULT 0,
    activated_grn_pending_amount DECIMAL(14,2)  NOT NULL DEFAULT 0,
    return_in_transit_amount     DECIMAL(14,2)  NOT NULL DEFAULT 0,
    utilized_amount              DECIMAL(14,2)  NOT NULL DEFAULT 0,

    -- refreshed daily (time-driven), plus on the sweep when the partner's stock moved
    aged_apple_stock_amount      DECIMAL(14,2)  NOT NULL DEFAULT 0,
    aged_apple_qty               INT            NOT NULL DEFAULT 0,
    demo_excluded_amount         DECIMAL(14,2)  NOT NULL DEFAULT 0,
    aged_refreshed_on            DATE               NULL,

    -- derived
    total_investment             DECIMAL(14,2)  NOT NULL DEFAULT 0,
    base_value                   DECIMAL(14,2)  NOT NULL DEFAULT 0,

    snapshot_timestamp           DATETIME       NOT NULL,

    PRIMARY KEY (fofo_id),
    KEY ix_partner_investment_snapshot (snapshot_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;