-- Customer Returns Migration
-- Run this file against the dms_db database

CREATE TABLE IF NOT EXISTS customer_returns (
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intCreatedBy INT NOT NULL,
    intSalesOrderId INT NOT NULL,
    intCustomerId INT NOT NULL,
    strReturnNo VARCHAR(50) NOT NULL UNIQUE,
    dtmReturnDate DATE NOT NULL,
    dblTotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    strStatus ENUM('PENDING','APPROVED','REJECTED','COMPLETED') NOT NULL DEFAULT 'PENDING',
    strReason TEXT,
    txtRemarks TEXT,
    dtmCreated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (intCreatedBy) REFERENCES users(intId),
    FOREIGN KEY (intSalesOrderId) REFERENCES sales_orders(intId),
    FOREIGN KEY (intCustomerId) REFERENCES customers(intId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS return_items (
    intId INT AUTO_INCREMENT PRIMARY KEY,
    intReturnId INT NOT NULL,
    intProductId INT NOT NULL,
    dblQty DECIMAL(18,2) NOT NULL,
    dblPrice DECIMAL(18,2) NOT NULL,
    strReason TEXT,
    FOREIGN KEY (intReturnId) REFERENCES customer_returns(intId) ON DELETE CASCADE,
    FOREIGN KEY (intProductId) REFERENCES products(intId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE customer_returns ADD COLUMN dblRefundAmount DECIMAL(18,2) NOT NULL DEFAULT 0 AFTER dblTotal;

ALTER TABLE customers ADD COLUMN dblCredit DECIMAL(18,2) NOT NULL DEFAULT 0 AFTER txtAddress;

ALTER TABLE payments MODIFY strMethod ENUM('CASH','BANK','CARD','CHEQUE','CREDIT') NOT NULL;

ALTER TABLE return_items ADD COLUMN strCondition VARCHAR(20) DEFAULT NULL AFTER strReason;
ALTER TABLE return_items ADD COLUMN txtAuditNote TEXT DEFAULT NULL AFTER strCondition;
ALTER TABLE return_items ADD COLUMN intRestock TINYINT(1) NOT NULL DEFAULT 1 AFTER txtAuditNote;

ALTER TABLE stock_transactions ADD COLUMN strReference VARCHAR(100) DEFAULT NULL AFTER strTransactionType;

CREATE INDEX idx_return_no ON customer_returns(strReturnNo);
CREATE INDEX idx_return_sales ON customer_returns(intSalesOrderId);