-- Fuel Card Integration: card providers, card master data, transaction log

CREATE TABLE IF NOT EXISTS fuel_card_providers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(40) NULL,
    contact_phone VARCHAR(30) NULL,
    website VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_fcp_name (name),
    UNIQUE KEY uq_fcp_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO fuel_card_providers (name, code) VALUES
('NNPC', 'NNPC'),
('TotalEnergies', 'TOTAL'),
('Mobil', 'MOBIL'),
('Oando', 'OANDO'),
('Conoil', 'CONOIL'),
('MRS', 'MRS'),
('Forte Oil', 'FORTE'),
('A-Z Petroleum', 'AZ'),
('NIPCO', 'NIPCO'),
('Eterna', 'ETERNA');

CREATE TABLE IF NOT EXISTS fuel_cards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    card_number VARCHAR(50) NOT NULL,
    card_number_masked VARCHAR(25) NOT NULL,
    card_type ENUM('physical','virtual') NOT NULL DEFAULT 'physical',
    provider_id BIGINT UNSIGNED NULL,
    assigned_to_type ENUM('vehicle','driver','none') DEFAULT 'none',
    assigned_to_id BIGINT UNSIGNED DEFAULT NULL,
    pin_hash VARCHAR(255) DEFAULT NULL,
    monthly_limit DECIMAL(14,2) DEFAULT NULL,
    per_transaction_limit DECIMAL(14,2) DEFAULT NULL,
    current_balance DECIMAL(14,2) DEFAULT 0.00,
    status ENUM('active','suspended','lost','cancelled','expired') NOT NULL DEFAULT 'active',
    issued_date DATE DEFAULT NULL,
    expiry_date DATE DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY uq_fc_number (card_number),
    INDEX idx_fc_provider (provider_id),
    INDEX idx_fc_status (status),
    INDEX idx_fc_assigned (assigned_to_type, assigned_to_id),
    CONSTRAINT fk_fc_provider FOREIGN KEY (provider_id) REFERENCES fuel_card_providers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fuel_card_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fuel_card_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('purchase','refund','credit','adjustment','fee') NOT NULL DEFAULT 'purchase',
    amount DECIMAL(14,2) NOT NULL,
    balance_before DECIMAL(14,2) DEFAULT NULL,
    balance_after DECIMAL(14,2) DEFAULT NULL,
    reference VARCHAR(100) DEFAULT NULL,
    vehicle_fuel_entry_id BIGINT UNSIGNED DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_by_user_id BIGINT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fct_card (fuel_card_id),
    INDEX idx_fct_entry (vehicle_fuel_entry_id),
    INDEX idx_fct_created (created_at),
    CONSTRAINT fk_fct_card FOREIGN KEY (fuel_card_id) REFERENCES fuel_cards(id) ON DELETE CASCADE,
    CONSTRAINT fk_fct_entry FOREIGN KEY (vehicle_fuel_entry_id) REFERENCES vehicle_fuel_entries(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE vehicle_fuel_entries
    ADD COLUMN IF NOT EXISTS fuel_card_id BIGINT UNSIGNED NULL AFTER card_balance,
    ADD INDEX idx_vfe_card (fuel_card_id),
    ADD CONSTRAINT fk_vfe_card FOREIGN KEY (fuel_card_id) REFERENCES fuel_cards(id) ON DELETE SET NULL;

INSERT IGNORE INTO permissions (code, description) VALUES
('fuel_card.view', 'View fuel cards'),
('fuel_card.create', 'Create and edit fuel cards');