CREATE DATABASE IF NOT EXISTS imarapay_db;
USE imarapay_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    wallet_balance DECIMAL(15, 2) DEFAULT 0.00,
    referral_code VARCHAR(20) UNIQUE DEFAULT NULL,
    referred_by INT DEFAULT NULL,
    gender VARCHAR(10) DEFAULT NULL,
    country VARCHAR(100) DEFAULT NULL,
    state VARCHAR(100) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    occupation VARCHAR(100) DEFAULT NULL,
    is_2fa_enabled TINYINT(1) DEFAULT 0,
    biometrics_enabled TINYINT(1) DEFAULT 0,
    two_factor_code VARCHAR(10) DEFAULT NULL,
    two_factor_expires_at DATETIME DEFAULT NULL,
    tier_level INT DEFAULT 1,
    nin_slip_image VARCHAR(255) DEFAULT NULL,
    nin_status ENUM('none', 'pending', 'approved', 'rejected') DEFAULT 'none',
    reset_token VARCHAR(10) DEFAULT NULL,
    reset_token_expires_at DATETIME DEFAULT NULL,
    expo_push_token VARCHAR(255) DEFAULT NULL,
    push_notifications TINYINT(1) DEFAULT 1,
    email_notifications TINYINT(1) DEFAULT 1,
    transaction_alerts TINYINT(1) DEFAULT 1,
    security_alerts TINYINT(1) DEFAULT 1,
    promotional_messages TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('buy', 'sell', 'fund', 'withdraw', 'bill') NOT NULL,
    title VARCHAR(255) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'NGN',
    receipt_url VARCHAR(500) NULL,
    card_pin VARCHAR(255) NULL,
    card_image_url VARCHAR(500) NULL,
    crypto_currency VARCHAR(20) NULL,
    crypto_amount DECIMAL(18, 8) NULL,
    wallet_address VARCHAR(255) NULL,
    payment_method ENUM('balance', 'transfer') NULL,
    status ENUM('pending', 'received', 'processing', 'completed', 'failed') DEFAULT 'completed',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_banks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bank_name VARCHAR(255) NOT NULL,
    bank_code VARCHAR(50) NOT NULL,
    account_number VARCHAR(50) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Trigger to automatically update wallet and send notification when transaction is approved
DELIMITER //

DROP TRIGGER IF EXISTS after_transaction_update //

CREATE TRIGGER after_transaction_update
AFTER UPDATE ON transactions
FOR EACH ROW
BEGIN
    IF OLD.status = 'pending' AND NEW.status = 'completed' THEN
        IF NEW.type = 'fund' THEN
            -- Update user's wallet
            UPDATE users SET wallet_balance = wallet_balance + NEW.amount WHERE id = NEW.user_id;
            
            -- Insert notification
            INSERT INTO notifications (user_id, title, message) 
            VALUES (NEW.user_id, 'Deposit Approved', CONCAT('Your deposit of ₦', FORMAT(NEW.amount, 2), ' was approved and credited to your wallet.'));
            
        ELSEIF NEW.type = 'sell' THEN
            -- Update user's wallet (credit)
            UPDATE users SET wallet_balance = wallet_balance + NEW.amount WHERE id = NEW.user_id;
            
            -- Insert notification
            INSERT INTO notifications (user_id, title, message) 
            VALUES (NEW.user_id, 'Gift Card Sale Approved', CONCAT('Your gift card sale was approved! ₦', FORMAT(NEW.amount, 2), ' has been credited to your wallet.'));
            
        ELSEIF NEW.type = 'withdraw' THEN
            -- Update user's wallet (deduct)
            UPDATE users SET wallet_balance = wallet_balance - NEW.amount WHERE id = NEW.user_id;
            
            -- Insert notification
            INSERT INTO notifications (user_id, title, message) 
            VALUES (NEW.user_id, 'Withdrawal Approved', CONCAT('Your withdrawal of ₦', FORMAT(NEW.amount, 2), ' was approved and debited from your wallet.'));
            
        END IF;
    END IF;
END //

DELIMITER ;

CREATE TABLE IF NOT EXISTS transaction_issues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    user_id INT NOT NULL,
    issue_text TEXT NOT NULL,
    status ENUM('open', 'resolved') DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    reset_token VARCHAR(10) DEFAULT NULL,
    reset_token_expires_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS giftcards_inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    image_url VARCHAR(500) NULL,
    category VARCHAR(100) NOT NULL,
    type VARCHAR(100) NOT NULL,
    denominations TEXT NOT NULL,
    buy_rate DECIMAL(15,2) NOT NULL,
    sell_rate DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cryptos_inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    symbol VARCHAR(50) NOT NULL UNIQUE,
    image_url VARCHAR(500) NULL,
    buy_rate DECIMAL(18,2) NOT NULL,
    sell_rate DECIMAL(18,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_email VARCHAR(255) DEFAULT 'support@imarapay.com',
    site_phone VARCHAR(50) DEFAULT '+2348000000000',
    smtp_host VARCHAR(255) DEFAULT 'imarapay.ygmotors.ng',
    smtp_user VARCHAR(255) DEFAULT 'noreply@imarapay.ygmotors.ng',
    smtp_pass VARCHAR(255) DEFAULT 'Terminal2026#',
    smtp_port INT DEFAULT 465,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS api_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    endpoint VARCHAR(500) NOT NULL,
    api_key TEXT NOT NULL,
    status TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);