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

CREATE TABLE IF NOT EXISTS djs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    profile_picture VARCHAR(255) DEFAULT NULL,
    qr_token VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS dj_balances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dj_id INT NOT NULL UNIQUE,
    available_balance DECIMAL(10,2) DEFAULT 0.00,
    pending_payout DECIMAL(10,2) DEFAULT 0.00,
    total_paid_out DECIMAL(10,2) DEFAULT 0.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dj_id) REFERENCES djs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS song_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dj_id INT NOT NULL,
    requester_name VARCHAR(100) NOT NULL,
    song_title VARCHAR(255) NOT NULL,
    tip_amount DECIMAL(10,2) NOT NULL,
    platform_fee DECIMAL(10,2) DEFAULT 0.00,
    dj_earnings DECIMAL(10,2) DEFAULT 0.00,
    request_status ENUM(
        'PENDING_DJ_APPROVAL',
        'ACCEPTED_BY_DJ',
        'WAITING_FOR_PAYMENT',
        'PAID',
        'PLAYED',
        'REJECTED_BY_DJ',
        'CANCELLED_BY_GUEST',
        'EXPIRED'
    ) DEFAULT 'PENDING_DJ_APPROVAL',
    payment_status ENUM('NONE','PENDING','PAID','FAILED','REFUNDED') DEFAULT 'NONE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dj_id) REFERENCES djs(id) ON DELETE CASCADE,
    INDEX idx_dj_status (dj_id, request_status, payment_status),
    INDEX idx_created_at (created_at)
);

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    payment_type VARCHAR(50) DEFAULT 'FAKE_PAYMENT',
    amount DECIMAL(10,2) NOT NULL,
    payment_status ENUM('PENDING','PAID','FAILED','REFUNDED') DEFAULT 'PENDING',
    provider_reference VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    FOREIGN KEY (request_id) REFERENCES song_requests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payout_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dj_id INT NOT NULL UNIQUE,
    payout_method ENUM('GCASH','MAYA','BANK') NOT NULL,
    account_name VARCHAR(150) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    bank_name VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (dj_id) REFERENCES djs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dj_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('PENDING','PROCESSING','PAID','FAILED','CANCELLED') DEFAULT 'PENDING',
    provider_reference VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    FOREIGN KEY (dj_id) REFERENCES djs(id) ON DELETE CASCADE
);