-- Database schema for Flight Refund System
-- Create database first: CREATE DATABASE refund_system;

CREATE DATABASE IF NOT EXISTS refund_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE refund_system;

-- Table: Flights (Data penerbangan yang diinput user)
CREATE TABLE IF NOT EXISTS flights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    flight_number VARCHAR(20) NOT NULL,
    airline VARCHAR(100) NOT NULL,
    from_airport VARCHAR(10) NOT NULL,
    from_city VARCHAR(100) NOT NULL,
    to_airport VARCHAR(10) NOT NULL,
    to_city VARCHAR(100) NOT NULL,
    price DECIMAL(15, 2) NOT NULL,
    class VARCHAR(50) DEFAULT 'Economy',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_flight_number (flight_number),
    INDEX idx_from_airport (from_airport),
    INDEX idx_to_airport (to_airport),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: Refunds (Data pengajuan refund)
CREATE TABLE IF NOT EXISTS refunds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    flight_id INT NOT NULL,
    ref_number VARCHAR(50) UNIQUE NOT NULL,
    passenger_name VARCHAR(100) NOT NULL,
    passenger_email VARCHAR(100) NOT NULL,
    passenger_phone VARCHAR(20) NOT NULL,
    bank_name VARCHAR(100),
    account_number VARCHAR(50),
    account_holder VARCHAR(100),
    payment_method ENUM('bank_transfer', 'credit_card') NOT NULL,
    card_number VARCHAR(20),
    card_expiry VARCHAR(10),
    card_cvv VARCHAR(10),
    cancellation_code VARCHAR(20),
    status ENUM('pending', 'verified', 'completed', 'cancelled') DEFAULT 'pending',
    refund_amount DECIMAL(15, 2) NOT NULL,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (flight_id) REFERENCES flights(id) ON DELETE CASCADE,
    INDEX idx_ref_number (ref_number),
    INDEX idx_status (status),
    INDEX idx_submitted_at (submitted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: QRIS Images (Untuk admin upload QRIS)
CREATE TABLE IF NOT EXISTS qris_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image_path VARCHAR(255) NOT NULL,
    uploaded_by VARCHAR(100),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    INDEX idx_uploaded_at (uploaded_at),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for testing
INSERT INTO flights (flight_number, airline, from_airport, from_city, to_airport, to_city, price, class) VALUES
('QR819', 'Qatar Airways', 'CGK', 'Jakarta', 'DOH', 'Doha', 8500000.00, 'Economy'),
('GA123', 'Garuda Indonesia', 'CGK', 'Jakarta', 'SIN', 'Singapore', 3200000.00, 'Business'),
('SQ956', 'Singapore Airlines', 'SIN', 'Singapore', 'CGK', 'Jakarta', 4500000.00, 'Economy'),
('EK398', 'Emirates', 'CGK', 'Jakarta', 'DXB', 'Dubai', 12000000.00, 'First Class');