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)
);