CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    account_name VARCHAR(100) NOT NULL,
    masked_number VARCHAR(30) NOT NULL,
    balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status ENUM('active','frozen','closed') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_accounts_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    INDEX idx_accounts_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS recipients (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    account_number VARCHAR(100) NOT NULL,
    routing_number VARCHAR(100) NULL,
    email VARCHAR(190) NULL,
    note VARCHAR(255) NULL,
    status ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_recipients_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    INDEX idx_recipients_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NULL,
    transaction_type ENUM('credit','debit') NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    fee DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    account_name VARCHAR(100) NULL,
    recipient_name VARCHAR(150) NULL,
    recipient_account VARCHAR(100) NULL,
    routing_number VARCHAR(100) NULL,
    recipient_email VARCHAR(190) NULL,
    receiving_bank VARCHAR(30) NULL,
    schedule_type VARCHAR(30) NULL,
    schedule_date DATE NULL,
    recurrence VARCHAR(30) NULL,
    memo VARCHAR(255) NULL,
    status ENUM('pending','held','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
    reference VARCHAR(40) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_transactions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_transactions_account
        FOREIGN KEY (account_id) REFERENCES accounts(id)
        ON DELETE SET NULL,
    INDEX idx_transactions_user_date (user_id, created_at),
    INDEX idx_transactions_account (account_id, created_at)
) ENGINE=InnoDB;

-- Example user matching the username displayed by the supplied page.
-- Change the username/full_name to match your actual login records.
INSERT INTO users (username, full_name, email)
SELECT 'Nia Morgan', 'Nia Morgan', NULL
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'Nia Morgan');

-- Seed the supplied checking balance once.
-- Do NOT run this INSERT repeatedly in production.
INSERT INTO accounts (user_id, account_name, masked_number, balance, currency)
SELECT u.id, 'Checking', '****-0079', 1783586.24, 'USD'
FROM users u
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (
      SELECT 1 FROM accounts a
      WHERE a.user_id = u.id AND a.masked_number = '****-0079'
  );

-- Seed the existing transactions from the supplied page.
INSERT INTO transactions
(user_id, account_id, transaction_type, description, amount, fee, currency, account_name, status, reference, created_at)
SELECT u.id, a.id, 'credit',
       'Deposit to Checkings from Sale of gold bars by Ghana Gold Board',
       891793.12, 0, 'USD', 'Checkings', 'completed', 'SEED-GGB-0817-1211',
       '2026-08-17 12:11:00'
FROM users u JOIN accounts a ON a.user_id = u.id AND a.masked_number = '****-0079'
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (SELECT 1 FROM transactions WHERE reference = 'SEED-GGB-0817-1211');

INSERT INTO transactions
(user_id, account_id, transaction_type, description, amount, fee, currency, account_name, status, reference, created_at)
SELECT u.id, a.id, 'credit',
       'Deposit to Checkings from Sale of gold bars by Ghana Gold Board',
       891793.12, 0, 'USD', 'Checkings', 'completed', 'SEED-GGB-0817-1322',
       '2026-08-17 13:22:00'
FROM users u JOIN accounts a ON a.user_id = u.id AND a.masked_number = '****-0079'
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (SELECT 1 FROM transactions WHERE reference = 'SEED-GGB-0817-1322');

INSERT INTO transactions
(user_id, account_id, transaction_type, description, amount, fee, currency, account_name, status, reference, created_at)
SELECT u.id, a.id, 'debit',
       'Debit from Checking account for monthly maintenance fee (July)',
       8.16, 0, 'USD', 'Checkings', 'completed', 'SEED-FEE-2026-07',
       '2026-07-26 08:55:00'
FROM users u JOIN accounts a ON a.user_id = u.id AND a.masked_number = '****-0079'
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (SELECT 1 FROM transactions WHERE reference = 'SEED-FEE-2026-07');

INSERT INTO transactions
(user_id, account_id, transaction_type, description, amount, fee, currency, account_name, status, reference, created_at)
SELECT u.id, a.id, 'debit',
       'Debit from Checking account for monthly maintenance fee (June)',
       8.16, 0, 'USD', 'Checkings', 'completed', 'SEED-FEE-2026-06',
       '2026-06-28 14:26:00'
FROM users u JOIN accounts a ON a.user_id = u.id AND a.masked_number = '****-0079'
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (SELECT 1 FROM transactions WHERE reference = 'SEED-FEE-2026-06');

INSERT INTO transactions
(user_id, account_id, transaction_type, description, amount, fee, currency, account_name, status, reference, created_at)
SELECT u.id, a.id, 'credit',
       'Opening deposit to Checkings',
       300.00, 0, 'USD', 'Checkings', 'completed', 'SEED-OPENING-0605',
       '2026-06-05 12:42:00'
FROM users u JOIN accounts a ON a.user_id = u.id AND a.masked_number = '****-0079'
WHERE u.username = 'Nia Morgan'
  AND NOT EXISTS (SELECT 1 FROM transactions WHERE reference = 'SEED-OPENING-0605');