Subversion Repositories SmartDukaan

-- Flagship credit: per-device tracking via a child table (instead of denormalizing IMEI onto transaction.loan).

--

-- A mixed flagship invoice creates ONE combined flagship limit row in transaction.loan, plus one

-- loan_imei row per device it covers. On sale/activation the sold IMEI's row is converted to a real

-- loan (converting only that unit's credit); same-model siblings keep their interest-free window.

-- Keeping transaction.loan at one-row-per-invoice avoids disturbing the many loan-row consumers

-- (statements, reco, utilization, selectLoanByInvoice).

--

-- Additive (new table only); safe to run on any environment before deploying the code.

CREATE TABLE IF NOT EXISTS transaction.loan_imei (

    id                INT AUTO_INCREMENT PRIMARY KEY,

    loan_id           INT NOT NULL,            -- the flagship limit row (transaction.loan.id)

    imei              VARCHAR(64) NOT NULL,    -- the device serial this slice covers

    amount            DECIMAL(12,4) NOT NULL,  -- this unit's credit amount

    converted_on      DATETIME NULL,           -- NULL = still interest-free under the limit

    converted_loan_id INT NULL,                -- the real loan created on conversion (audit)

    created_on        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX ix_loan_imei_loan (loan_id),

    INDEX ix_loan_imei_imei (imei)

);