

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
);
