CREATE DATABASE IF NOT EXISTS imarapay_db;
USE imarapay_db;

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
);
            
            -- 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.'));
            
            -- 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.'));
            
            -- 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 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
);
