-- Transaction Processing Lifecycle Tables
-- These three tables support the transaction lifecycle: temp -> success/failed

-- Create pos_temp_transaction table for transactions in progress
CREATE TABLE pos_temp_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    s_txn_type VARCHAR(25) COMMENT 'Source Txn Type',
    s_tid VARCHAR(8) COMMENT 'Source Terminal ID (POS)',
    s_mid VARCHAR(15) COMMENT 'Source Merchant ID (POS)',
    s_tid_stan VARCHAR(6) COMMENT 'Source Terminal STAN',
    s_tid_invoiceno VARCHAR(6) COMMENT 'Source Terminal Invoice Number',
    s_tid_batchno VARCHAR(6) COMMENT 'Source Terminal Batch Number',
    b_tid VARCHAR(8) COMMENT 'Bank Terminal ID (Acquirer)',
    b_mid VARCHAR(15) COMMENT 'Bank Merchant ID (Acquirer)',
    acquirer_id BIGINT COMMENT 'Bank/Acquirer ID (Acquirer)',
    b_tid_stan VARCHAR(6) COMMENT 'Bank Terminal STAN',
    b_tid_invoiceno VARCHAR(6) COMMENT 'Bank Terminal Invoice Number',
    b_tid_batchno VARCHAR(6) COMMENT 'Bank Terminal Batch Number',
    b_tid_date VARCHAR(8) COMMENT 'Bank Terminal Date',
    b_tid_time VARCHAR(6) COMMENT 'Bank Terminal Time',
    entry_mode VARCHAR(3) COMMENT 'Card Entry Mode',
    condition_code VARCHAR(2) COMMENT 'Condition Code',
    currency_code VARCHAR(3) COMMENT 'Currency Code',
    mti VARCHAR(4) COMMENT 'Message Type Indicator',
    proc_code VARCHAR(6) COMMENT 'Processing Code',
    total_amount DECIMAL(12,2) COMMENT 'Total Amount',
    auth_amount DECIMAL(12,2) COMMENT 'Authorization Amount',
    cash_amount DECIMAL(12,2) COMMENT 'Cash Amount',
    tip_amount DECIMAL(12,2) COMMENT 'Tip Amount',
    approval_code VARCHAR(6) COMMENT 'Authorization Approval Code',
    reference_no VARCHAR(12) COMMENT 'Reference Number',
    response_code VARCHAR(2) COMMENT 'Response Code',
    response_message VARCHAR(100) COMMENT 'Response Message',
    mcc_code VARCHAR(4) COMMENT 'Merchant Category Code',
    encrypted_track2 VARCHAR(256) COMMENT 'Encrypted Track 2 Data',
    encrypted_pan VARCHAR(256) COMMENT 'Encrypted PAN',
    masked_card_no VARCHAR(32) COMMENT 'Masked Card Number',
    encrypted_expiry VARCHAR(128) COMMENT 'Encrypted Expiry Date',
    pan_seq VARCHAR(3) COMMENT 'PAN Sequence Number',
    emv_data TEXT COMMENT 'EMV Data',
    acquirer_reference_no VARCHAR(23) COMMENT 'Acquirer Reference Number',
    scheme_reference_no VARCHAR(23) COMMENT 'Scheme Reference Number',
    created_dateTime DATETIME COMMENT 'Creation Timestamp',
    updated_dateTime DATETIME COMMENT 'Last Update Timestamp',
    status VARCHAR(45) COMMENT 'status of the transaction e.g. IN_PROGRESS, COMPLETED, FAILED',
    metadata TEXT COMMENT 'Additional Metadata'
);

-- Create pos_transaction table for successful transactions
CREATE TABLE pos_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    s_txn_type VARCHAR(25) COMMENT 'Source Txn Type',
    s_tid VARCHAR(8) COMMENT 'Source Terminal ID (POS)',
    s_mid VARCHAR(15) COMMENT 'Source Merchant ID (POS)',
    s_tid_stan VARCHAR(6) COMMENT 'Source Terminal STAN',
    s_tid_invoiceno VARCHAR(6) COMMENT 'Source Terminal Invoice Number',
    s_tid_batchno VARCHAR(6) COMMENT 'Source Terminal Batch Number',
    b_tid VARCHAR(8) COMMENT 'Bank Terminal ID (Acquirer)',
    b_mid VARCHAR(15) COMMENT 'Bank Merchant ID (Acquirer)',
    acquirer_id BIGINT COMMENT 'Bank/Acquirer ID (Acquirer)',
    b_tid_stan VARCHAR(6) COMMENT 'Bank Terminal STAN',
    b_tid_invoiceno VARCHAR(6) COMMENT 'Bank Terminal Invoice Number',
    b_tid_batchno VARCHAR(6) COMMENT 'Bank Terminal Batch Number',
    b_tid_date VARCHAR(8) COMMENT 'Bank Terminal Date',
    b_tid_time VARCHAR(6) COMMENT 'Bank Terminal Time',
    entry_mode VARCHAR(3) COMMENT 'Card Entry Mode',
    condition_code VARCHAR(2) COMMENT 'Condition Code',
    currency_code VARCHAR(3) COMMENT 'Currency Code',
    mti VARCHAR(4) COMMENT 'Message Type Indicator',
    proc_code VARCHAR(6) COMMENT 'Processing Code',
    total_amount DECIMAL(12,2) COMMENT 'Total Amount',
    auth_amount DECIMAL(12,2) COMMENT 'Authorization Amount',
    cash_amount DECIMAL(12,2) COMMENT 'Cash Amount',
    tip_amount DECIMAL(12,2) COMMENT 'Tip Amount',
    approval_code VARCHAR(6) COMMENT 'Authorization Approval Code',
    reference_no VARCHAR(12) COMMENT 'Reference Number',
    response_code VARCHAR(2) COMMENT 'Response Code',
    response_message VARCHAR(100) COMMENT 'Response Message',
    mcc_code VARCHAR(4) COMMENT 'Merchant Category Code',
    encrypted_track2 VARCHAR(256) COMMENT 'Encrypted Track 2 Data',
    encrypted_pan VARCHAR(256) COMMENT 'Encrypted PAN',
    masked_card_no VARCHAR(32) COMMENT 'Masked Card Number',
    encrypted_expiry VARCHAR(128) COMMENT 'Encrypted Expiry Date',
    pan_seq VARCHAR(3) COMMENT 'PAN Sequence Number',
    emv_data TEXT COMMENT 'EMV Data',
    acquirer_reference_no VARCHAR(23) COMMENT 'Acquirer Reference Number',
    scheme_reference_no VARCHAR(23) COMMENT 'Scheme Reference Number',
    created_dateTime DATETIME COMMENT 'Creation Timestamp',
    updated_dateTime DATETIME COMMENT 'Last Update Timestamp',
    metadata TEXT COMMENT 'Additional Metadata'
);

-- Create pos_failed_transaction table for failed transactions
CREATE TABLE pos_failed_transaction (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    s_txn_type VARCHAR(25) COMMENT 'Source Txn Type',
    s_tid VARCHAR(8) COMMENT 'Source Terminal ID (POS)',
    s_mid VARCHAR(15) COMMENT 'Source Merchant ID (POS)',
    s_tid_stan VARCHAR(6) COMMENT 'Source Terminal STAN',
    s_tid_invoiceno VARCHAR(6) COMMENT 'Source Terminal Invoice Number',
    s_tid_batchno VARCHAR(6) COMMENT 'Source Terminal Batch Number',
    b_tid VARCHAR(8) COMMENT 'Bank Terminal ID (Acquirer)',
    b_mid VARCHAR(15) COMMENT 'Bank Merchant ID (Acquirer)',
    acquirer_id BIGINT COMMENT 'Bank/Acquirer ID (Acquirer)',
    b_tid_stan VARCHAR(6) COMMENT 'Bank Terminal STAN',
    b_tid_invoiceno VARCHAR(6) COMMENT 'Bank Terminal Invoice Number',
    b_tid_batchno VARCHAR(6) COMMENT 'Bank Terminal Batch Number',
    b_tid_date VARCHAR(8) COMMENT 'Bank Terminal Date',
    b_tid_time VARCHAR(6) COMMENT 'Bank Terminal Time',
    entry_mode VARCHAR(3) COMMENT 'Card Entry Mode',
    condition_code VARCHAR(2) COMMENT 'Condition Code',
    currency_code VARCHAR(3) COMMENT 'Currency Code',
    mti VARCHAR(4) COMMENT 'Message Type Indicator',
    proc_code VARCHAR(6) COMMENT 'Processing Code',
    total_amount DECIMAL(12,2) COMMENT 'Total Amount',
    auth_amount DECIMAL(12,2) COMMENT 'Authorization Amount',
    cash_amount DECIMAL(12,2) COMMENT 'Cash Amount',
    tip_amount DECIMAL(12,2) COMMENT 'Tip Amount',
    approval_code VARCHAR(6) COMMENT 'Authorization Approval Code',
    reference_no VARCHAR(12) COMMENT 'Reference Number',
    response_code VARCHAR(2) COMMENT 'Response Code',
    response_message VARCHAR(100) COMMENT 'Response Message',
    mcc_code VARCHAR(4) COMMENT 'Merchant Category Code',
    encrypted_track2 VARCHAR(256) COMMENT 'Encrypted Track 2 Data',
    encrypted_pan VARCHAR(256) COMMENT 'Encrypted PAN',
    masked_card_no VARCHAR(32) COMMENT 'Masked Card Number',
    encrypted_expiry VARCHAR(128) COMMENT 'Encrypted Expiry Date',
    pan_seq VARCHAR(3) COMMENT 'PAN Sequence Number',
    emv_data TEXT COMMENT 'EMV Data',
    acquirer_reference_no VARCHAR(23) COMMENT 'Acquirer Reference Number',
    scheme_reference_no VARCHAR(23) COMMENT 'Scheme Reference Number',
    created_dateTime DATETIME COMMENT 'Creation Timestamp',
    updated_dateTime DATETIME COMMENT 'Last Update Timestamp',
    metadata TEXT COMMENT 'Additional Metadata'
);

CREATE TABLE pos_transaction_reversal (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    s_txn_type VARCHAR(25) COMMENT 'Source Txn Type',
    original_temp_txn_id BIGINT NOT NULL,
    reversal_reason VARCHAR(50) NOT NULL,
    reversal_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    s_tid VARCHAR(8),
    s_mid VARCHAR(15),
    s_tid_stan VARCHAR(6),
    b_tid VARCHAR(8),
    b_mid VARCHAR(15),
    acquirer_id BIGINT,
    b_tid_stan VARCHAR(6),
    reversal_mti VARCHAR(4),
    original_mti VARCHAR(4),
    original_encrypted_pan VARCHAR(128),
    original_encrypted_expiry_date VARCHAR(128),
    original_proc_code VARCHAR(6),
    original_amount DECIMAL(12,2),
    original_stan VARCHAR(6),
    original_time VARCHAR(6),
    original_date VARCHAR(8),
    original_entry_mode VARCHAR(3),
    original_pan_seq VARCHAR(3),
    original_reference_no VARCHAR(12),
    original_currency_code VARCHAR(3),
    original_batch_no VARCHAR(12),
    reversal_request TEXT,
    reversal_response TEXT,
    reversal_response_code VARCHAR(2),
    reversal_reference_no VARCHAR(12),
    retry_count INT NOT NULL DEFAULT 0,
    max_retry_attempts INT NOT NULL DEFAULT 3,
    next_retry_time DATETIME,
    created_dateTime DATETIME NOT NULL,
    initiated_dateTime DATETIME,
    completed_dateTime DATETIME,
    updated_dateTime DATETIME,
    error_message VARCHAR(255),
    metadata TEXT
);

-- Create indexes for optimal performance

-- Indexes for pos_temp_transaction (critical for terminal busy checks)
CREATE INDEX idx_temp_transaction_btid_bmid ON pos_temp_transaction(b_tid, b_mid);
CREATE INDEX idx_temp_transaction_stid ON pos_temp_transaction(s_tid);
CREATE INDEX idx_temp_transaction_created ON pos_temp_transaction(created_dateTime);

-- Indexes for pos_transaction
CREATE INDEX idx_transaction_btid_bmid ON pos_transaction(b_tid, b_mid);
CREATE INDEX idx_transaction_stid ON pos_transaction(s_tid);
CREATE INDEX idx_transaction_created ON pos_transaction(created_dateTime);
CREATE INDEX idx_transaction_response_code ON pos_transaction(response_code);
CREATE INDEX idx_transaction_approval_code ON pos_transaction(approval_code);

-- Indexes for pos_failed_transaction
CREATE INDEX idx_failed_transaction_btid_bmid ON pos_failed_transaction(b_tid, b_mid);
CREATE INDEX idx_failed_transaction_stid ON pos_failed_transaction(s_tid);
CREATE INDEX idx_failed_transaction_created ON pos_failed_transaction(created_dateTime);
CREATE INDEX idx_failed_transaction_response_code ON pos_failed_transaction(response_code);

-- Indexes for pos_transaction_reversal
CREATE INDEX idx_reversal_btid_bmid on pos_transaction_reversal(b_tid, b_mid);
CREATE INDEX idx_reversal_original_txn on pos_transaction_reversal(original_temp_txn_id);
CREATE INDEX idx_reversal_status on pos_transaction_reversal(reversal_status);
CREATE INDEX idx_reversal_created on pos_transaction_reversal(created_dateTime);

-- Additional indexes for reporting and analysis
CREATE INDEX idx_transaction_amount ON pos_transaction(total_amount);
CREATE INDEX idx_transaction_mti ON pos_transaction(mti);
CREATE INDEX idx_failed_transaction_mti ON pos_failed_transaction(mti);

CREATE TABLE acquirer_terminal_state (
    id BIGINT PRIMARY KEY,
    acquirer_terminal_id BIGINT NOT NULL,
    is_busy BOOLEAN NOT NULL
);

CREATE UNIQUE INDEX idx_acquirer_terminal_id ON acquirre_terminal_state(acquirer_terminal_id);