-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    bank_id CHAR(6) NOT NULL UNIQUE,   -- Base 16 string, e.g., A1B2C3
    password VARCHAR(25) NOT NULL,     -- Minimum length to enforce in app logic
    pin1 CHAR(4) NOT NULL,             -- 4-digit PIN
    pin2 CHAR(5) NOT NULL              -- 5-digit PIN
);

-- Accounts table
CREATE TABLE account (
    bank_id CHAR(6) NOT NULL,
    iban CHAR(34) PRIMARY KEY,          -- IBAN max length is 34
    name VARCHAR(50) NOT NULL,          -- First name
    s_name VARCHAR(50) NOT NULL,        -- Surname
    account_name VARCHAR(100) NOT NULL, -- e.g., "Savings Account"
    balance DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL,          -- ISO currency code
    FOREIGN KEY (bank_id) REFERENCES users(bank_id)
);

-- Transactions table
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_iban CHAR(34) NOT NULL,
    to_iban CHAR(34) NOT NULL,
    amount DECIMAL(18,2) NOT NULL CHECK (amount > 0), -- Positive amount
    currency CHAR(3) NOT NULL,
    transfer_type ENUM('REQUEST', 'TRANSFER') NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    description CHAR(200),
    FOREIGN KEY (from_iban) REFERENCES account(iban),
    FOREIGN KEY (to_iban) REFERENCES account(iban)
);

-- Insert user
INSERT INTO users (bank_id, password, pin1, pin2)
VALUES ('A1B2C3', 'MySecurePassword123', '1234', '54321');

-- Insert a new account
INSERT INTO account (bank_id, iban, name, s_name, account_name, balance, currency)
VALUES ('A1B2C3', 'GB33BUKB20201555555555', 'John', 'Doe', 'Savings Account', 1000.00, 'USD');

-- Insert a new transaction
INSERT INTO transactions (from_iban, to_iban, amount, currency, transfer_type, description)
VALUES ('GB33BUKB20201555555555', 'GB44BARC20201566666666', 250.00, 'USD', 'TRANSFER', 'Payment for services');