USE null_terminal;

SET @add_currency := (
    SELECT IF(COUNT(*) = 0, "ALTER TABLE users ADD COLUMN preferred_currency CHAR(3) NOT NULL DEFAULT 'GHS' AFTER rank_points", 'SELECT 1')
    FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'preferred_currency'
);
PREPARE stmt_currency FROM @add_currency; EXECUTE stmt_currency; DEALLOCATE PREPARE stmt_currency;

CREATE TABLE IF NOT EXISTS currencies (
    code CHAR(3) PRIMARY KEY, name VARCHAR(80) NOT NULL, symbol VARCHAR(8) NOT NULL,
    paystack_supported TINYINT(1) NOT NULL DEFAULT 0, is_enabled TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;
INSERT IGNORE INTO currencies (code, name, symbol, paystack_supported) VALUES
('GHS','Ghanaian Cedi','GH₵',1),('NGN','Nigerian Naira','₦',1),('USD','US Dollar','$',1),
('ZAR','South African Rand','R',1),('GBP','Pound Sterling','£',0),('EUR','Euro','€',0);

CREATE TABLE IF NOT EXISTS exchange_rates (
    base_code CHAR(3) NOT NULL DEFAULT 'GHS', quote_code CHAR(3) NOT NULL,
    rate DECIMAL(18,8) NOT NULL, source VARCHAR(80) NOT NULL DEFAULT 'manual',
    fetched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (base_code, quote_code), FOREIGN KEY (base_code) REFERENCES currencies(code),
    FOREIGN KEY (quote_code) REFERENCES currencies(code)
) ENGINE=InnoDB;
INSERT IGNORE INTO exchange_rates (base_code, quote_code, rate, source) VALUES
('GHS','GHS',1,'system'),('GHS','NGN',110,'bootstrap'),('GHS','USD',0.065,'bootstrap'),
('GHS','ZAR',1.18,'bootstrap'),('GHS','GBP',0.051,'bootstrap'),('GHS','EUR',0.060,'bootstrap');

CREATE TABLE IF NOT EXISTS account_requests (
    id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(120) NOT NULL, email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL, requested_currency CHAR(3) NOT NULL DEFAULT 'GHS',
    status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', reviewed_by INT DEFAULT NULL,
    reviewed_at DATETIME DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;