-- SwissCapital fictional demo schema
-- Sample data only. No real banking, payments, or identity verification.
-- MySQL 8+ example schema.

CREATE DATABASE IF NOT EXISTS swisscapital_demo
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE swisscapital_demo;

CREATE TABLE IF NOT EXISTS demo_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    display_name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NULL COMMENT 'Store only a modern password hash if authentication is added',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS demo_accounts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    account_label VARCHAR(80) NOT NULL DEFAULT 'Demo account',
    currency CHAR(3) NOT NULL DEFAULT 'CHF',
    demo_balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_demo_accounts_user
      FOREIGN KEY (user_id) REFERENCES demo_users(id)
      ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS demo_transactions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    transaction_type ENUM('deposit_demo', 'withdrawal_demo', 'transfer_demo') NOT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(14,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'CHF',
    status ENUM('demo_record') NOT NULL DEFAULT 'demo_record',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_demo_transactions_account
      FOREIGN KEY (account_id) REFERENCES demo_accounts(id)
      ON DELETE CASCADE,
    CONSTRAINT chk_demo_transaction_amount CHECK (amount >= 0)
);

-- Optional clearly fictional sample records:
INSERT INTO demo_users (display_name, email)
VALUES ('Sample Demo User', 'sample@example.test')
ON DUPLICATE KEY UPDATE display_name = VALUES(display_name);

INSERT INTO demo_accounts (user_id, account_label, currency, demo_balance)
SELECT id, 'SwissCapital Demo Account', 'CHF', 1250.00
FROM demo_users
WHERE email = 'sample@example.test'
AND NOT EXISTS (
  SELECT 1 FROM demo_accounts a
  WHERE a.user_id = demo_users.id AND a.account_label = 'SwissCapital Demo Account'
);

INSERT INTO demo_transactions (account_id, transaction_type, description, amount, currency)
SELECT id, 'deposit_demo', 'Fictional opening demo balance', 1250.00, 'CHF'
FROM demo_accounts
WHERE account_label = 'SwissCapital Demo Account'
AND NOT EXISTS (
  SELECT 1 FROM demo_transactions t
  WHERE t.account_id = demo_accounts.id
    AND t.description = 'Fictional opening demo balance'
);