-- eTIMS Invoices Table
-- Stores eTIMS invoice records for audit and reference

CREATE TABLE IF NOT EXISTS etims_invoices (
    invoice_record_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Source Information
    source_id INT UNSIGNED NOT NULL COMMENT 'ID of the source record (premium_id, contribution_id, etc.)',
    source_type VARCHAR(50) NOT NULL COMMENT 'Type: PREMIUM, CONTRIBUTION, SERVICE_FEE, etc.',
    reference VARCHAR(100) DEFAULT NULL COMMENT 'Additional reference number',
    
    -- eTIMS Invoice Details
    invoice_number VARCHAR(50) NOT NULL COMMENT 'eTIMS invoice number',
    trader_invoice_no VARCHAR(100) DEFAULT NULL COMMENT 'Trader invoice number',
    receipt_signature TEXT DEFAULT NULL COMMENT 'eTIMS receipt signature',
    cu_serial VARCHAR(100) DEFAULT NULL COMMENT 'Control Unit serial number',
    
    -- Response Data
    etims_response TEXT DEFAULT NULL COMMENT 'Full eTIMS API response (JSON)',
    
    -- Status
    status ENUM('PENDING', 'SUBMITTED', 'FAILED', 'CANCELLED') DEFAULT 'PENDING',
    error_message TEXT DEFAULT NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_source (source_id, source_type),
    INDEX idx_invoice_number (invoice_number),
    INDEX idx_trader_invoice (trader_invoice_no),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='eTIMS invoice records for audit trail';

-- Credit Notes Table
CREATE TABLE IF NOT EXISTS etims_credit_notes (
    credit_note_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Original Invoice
    original_invoice_number VARCHAR(50) NOT NULL,
    original_invoice_record_id INT UNSIGNED DEFAULT NULL,
    
    -- Credit Note Details
    credit_note_number VARCHAR(50) NOT NULL,
    trader_invoice_no VARCHAR(100) DEFAULT NULL,
    reason TEXT DEFAULT NULL,
    
    -- Source Information
    source_id INT UNSIGNED NOT NULL,
    source_type VARCHAR(50) NOT NULL,
    
    -- Response Data
    etims_response TEXT DEFAULT NULL,
    
    -- Status
    status ENUM('PENDING', 'SUBMITTED', 'FAILED') DEFAULT 'PENDING',
    error_message TEXT DEFAULT NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (original_invoice_record_id) REFERENCES etims_invoices(invoice_record_id) ON DELETE SET NULL,
    INDEX idx_original_invoice (original_invoice_number),
    INDEX idx_credit_note_number (credit_note_number),
    INDEX idx_source (source_id, source_type),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='eTIMS credit note records';
