
CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    account_number VARCHAR(32) NOT NULL UNIQUE,
    routing_number VARCHAR(32) NOT NULL,
    debit_card_number VARCHAR(32) NOT NULL UNIQUE,
    debit_card_last_four VARCHAR(4) NOT NULL,
    debit_card_expiry_month TINYINT UNSIGNED NOT NULL DEFAULT 11,
    debit_card_expiry_year TINYINT UNSIGNED NOT NULL DEFAULT 27,
    debit_card_cvc VARCHAR(4) NOT NULL DEFAULT '875',
    main_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    debit_card_spending_limit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    spending_limit_until_date DATE DEFAULT NULL,
    debit_card_status TINYINT NOT NULL DEFAULT 0 COMMENT '0=pending,1=active,2=stolen/frozen',
    user_consent TINYINT(1) NOT NULL DEFAULT 0,
    profile_image VARCHAR(255) DEFAULT NULL,
    two_factor_auth VARCHAR(255) DEFAULT NULL,
    mobile_number VARCHAR(30) DEFAULT NULL,
    enable_2fa INT NOT NULL DEFAULT 0,
    verification_method_2fa ENUM('face_id','text_msg') DEFAULT NULL,
    activation_code VARCHAR(10) DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 0 COMMENT '0=inactive/unverified,1=active',
    device_model VARCHAR(100) DEFAULT NULL,
    app_version VARCHAR(50) DEFAULT NULL,
    ios_device_token VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS access_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    access_token VARCHAR(128) NOT NULL UNIQUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    CONSTRAINT fk_access_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS app_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_token VARCHAR(255) NOT NULL,
    app_environment VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_income (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    income_amount DECIMAL(15,2) NOT NULL,
    income_frequency VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_income_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_income_distribution (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    income_id BIGINT UNSIGNED NOT NULL,
    distribution_category VARCHAR(50) NOT NULL,
    monthly_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    per_paycheck_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_income_distribution_income FOREIGN KEY (income_id) REFERENCES user_income(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS cashpit_lists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    cashpit_name VARCHAR(150) NOT NULL,
    budget_category ENUM('rent','groceries','bills','car payment','savings','other') NOT NULL DEFAULT 'other',
    target_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    current_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    cashpit_account_number VARCHAR(32) NOT NULL UNIQUE,
    cashpit_routing_number VARCHAR(32) NOT NULL,
    merchant_name VARCHAR(150) DEFAULT NULL,
    merchant_account_number VARCHAR(50) DEFAULT NULL,
    merchant_routing_number VARCHAR(50) DEFAULT NULL,
    merchant_auto_pay_enable TINYINT(1) NOT NULL DEFAULT 0,
    merchant_monthly_payment_date DATETIME DEFAULT NULL,
    merchant_monthly_payment_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    auto_deposit_percent FLOAT NOT NULL DEFAULT 0,
    color_code VARCHAR(50) NOT NULL DEFAULT 'blue',
    lock_cashpit_until DATETIME DEFAULT NULL,
    cashpit_emergency_unlock_count INT NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cashpit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS debit_card_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    transaction_id VARCHAR(50) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    from_account VARCHAR(150) DEFAULT NULL,
    to_account VARCHAR(150) DEFAULT NULL,
    amount DECIMAL(15,2) NOT NULL,
    notes TEXT DEFAULT NULL,
    transaction_type ENUM('Withdraw','Deposit','Spend') NOT NULL,
    date_time_of_transaction DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_debit_tx_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS cashpit_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cashpit_id BIGINT UNSIGNED NOT NULL,
    history_title VARCHAR(150) NOT NULL,
    history_description TEXT DEFAULT NULL,
    history_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cashpit_history_cashpit FOREIGN KEY (cashpit_id) REFERENCES cashpit_lists(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS request_debit_card (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    mobile_number VARCHAR(30) DEFAULT NULL,
    shipping_address TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_request_card_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO admins (full_name, email, password)
SELECT 'Super Admin', 'admin@example.com', '$2y$10$rQOpk42vfE0/UvH8usxL9uSyQ16WjckA4lIqi0a7IZ4qfAyB0qeQK'
WHERE NOT EXISTS (SELECT 1 FROM admins WHERE email = 'admin@example.com');
