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;