-- Payments and Receipts Database Tables for WebBooks Travel ERP

-- Create payments table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    client_id INT NOT NULL,
    invoice_id INT NULL,
    booking_id INT NULL,
    payment_number VARCHAR(20) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'credit_card', 'debit_card', 'mobile_money', 'cheque', 'other') DEFAULT 'bank_transfer',
    reference_number VARCHAR(100) NULL,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'completed',
    notes TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agency (agency_id),
    INDEX idx_client (client_id),
    INDEX idx_invoice (invoice_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_status (status),
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create receipts table
CREATE TABLE IF NOT EXISTS receipts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NOT NULL,
    payment_id INT NOT NULL,
    client_id INT NOT NULL,
    invoice_id INT NULL,
    receipt_number VARCHAR(20) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_method VARCHAR(50) NULL,
    payment_reference VARCHAR(100) NULL,
    issued_date DATE NOT NULL,
    notes TEXT NULL,
    receipt_data JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_agency (agency_id),
    INDEX idx_payment (payment_id),
    INDEX idx_client (client_id),
    INDEX idx_receipt_number (receipt_number),
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample payment records for testing
INSERT INTO payments (agency_id, client_id, invoice_id, payment_number, amount, payment_date, payment_method, status, created_at) VALUES 
(1, 1, 1, 'PAY-0001', 1500.00, '2026-03-01', 'bank_transfer', 'completed', '2026-03-01 10:00:00'),
(1, 2, 2, 'PAY-0002', 2300.00, '2026-03-02', 'credit_card', 'completed', '2026-03-02 11:30:00'),
(1, 3, 3, 'PAY-0003', 850.00, '2026-03-05', 'cash', 'completed', '2026-03-05 09:15:00'),
(1, 1, 4, 'PAY-0004', 3200.00, '2026-03-07', 'bank_transfer', 'completed', '2026-03-07 14:20:00'),
(1, 4, NULL, 'PAY-0005', 500.00, '2026-03-08', 'mobile_money', 'pending', '2026-03-08 16:45:00');

-- Insert sample receipts for testing
INSERT INTO receipts (agency_id, payment_id, client_id, invoice_id, receipt_number, amount, payment_method, issued_date) VALUES
(1, 1, 1, 1, 'RCT-0001', 1500.00, 'bank_transfer', '2026-03-01'),
(1, 2, 2, 2, 'RCT-0002', 2300.00, 'credit_card', '2026-03-02'),
(1, 3, 3, 3, 'RCT-0003', 850.00, 'cash', '2026-03-05'),
(1, 4, 1, 4, 'RCT-0004', 3200.00, 'bank_transfer', '2026-03-07');

-- Update invoices paid_amount and status based on payments
UPDATE invoices i
SET i.paid_amount = COALESCE((
    SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id = i.id AND p.status = 'completed'
), 0),
i.status = CASE 
    WHEN COALESCE((
        SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id = i.id AND p.status = 'completed'
    ), 0) >= i.total THEN 'paid'
    WHEN COALESCE((
        SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id = i.id AND p.status = 'completed'
    ), 0) > 0 THEN 'partially_paid'
    ELSE i.status
END;