<?php

declare(strict_types=1);

namespace Budget;

final class Schema
{
    /** @return list<string> */
    public static function statements(string $driver): array
    {
        return $driver === 'mysql' ? self::mysql() : self::sqlite();
    }

    /** @return list<string> */
    private static function sqlite(): array
    {
        return [
            'CREATE TABLE IF NOT EXISTS settings (
                setting_key TEXT PRIMARY KEY,
                setting_value TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                totp_secret TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE TABLE IF NOT EXISTS monthly_plans (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL UNIQUE,
                starting_income NUMERIC NOT NULL DEFAULT 0,
                rollover_in NUMERIC NOT NULL DEFAULT 0,
                rolling_enabled INTEGER NOT NULL DEFAULT 1,
                rollover_source_month TEXT,
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE TABLE IF NOT EXISTS income_entries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL,
                label TEXT NOT NULL,
                amount NUMERIC NOT NULL,
                received_on TEXT NOT NULL,
                withholding_amount NUMERIC NOT NULL DEFAULT 0,
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE INDEX IF NOT EXISTS idx_income_month ON income_entries(month)',
            'CREATE TABLE IF NOT EXISTS categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                color TEXT NOT NULL DEFAULT "#7c5cff",
                monthly_limit NUMERIC NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE TABLE IF NOT EXISTS budget_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL,
                category_id INTEGER,
                name TEXT NOT NULL,
                planned_amount NUMERIC NOT NULL DEFAULT 0,
                due_date TEXT,
                recurrence TEXT NOT NULL DEFAULT "none",
                series_key TEXT,
                status TEXT NOT NULL DEFAULT "active",
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
            )',
            'CREATE INDEX IF NOT EXISTS idx_budget_items_month ON budget_items(month)',
            'CREATE UNIQUE INDEX IF NOT EXISTS idx_budget_items_series_month ON budget_items(series_key, month) WHERE series_key IS NOT NULL',
            'CREATE TABLE IF NOT EXISTS transactions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL,
                budget_item_id INTEGER,
                category_id INTEGER,
                merchant TEXT NOT NULL,
                amount NUMERIC NOT NULL,
                transacted_on TEXT NOT NULL,
                dead_pull_amount NUMERIC NOT NULL DEFAULT 0,
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (budget_item_id) REFERENCES budget_items(id) ON DELETE SET NULL,
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
            )',
            'CREATE INDEX IF NOT EXISTS idx_transactions_month ON transactions(month)',
            'CREATE TABLE IF NOT EXISTS dead_account_entries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL,
                kind TEXT NOT NULL,
                amount NUMERIC NOT NULL,
                source_type TEXT,
                source_id INTEGER,
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE INDEX IF NOT EXISTS idx_dead_month ON dead_account_entries(month)',
            'CREATE INDEX IF NOT EXISTS idx_dead_source ON dead_account_entries(source_type, source_id)',
            'CREATE TABLE IF NOT EXISTS calendar_events (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                month TEXT NOT NULL,
                title TEXT NOT NULL,
                event_date TEXT NOT NULL,
                event_type TEXT NOT NULL DEFAULT "note",
                notes TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE TABLE IF NOT EXISTS alert_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                budget_item_id INTEGER NOT NULL,
                alert_date TEXT NOT NULL,
                window_days INTEGER NOT NULL,
                recipient TEXT NOT NULL,
                status TEXT NOT NULL,
                response TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE (budget_item_id, alert_date, window_days, recipient),
                FOREIGN KEY (budget_item_id) REFERENCES budget_items(id) ON DELETE CASCADE
            )',
            'CREATE TABLE IF NOT EXISTS visits (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                route TEXT NOT NULL,
                visitor_hash TEXT NOT NULL,
                visited_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            )',
            'CREATE INDEX IF NOT EXISTS idx_visits_date ON visits(visited_at)',
            'CREATE TABLE IF NOT EXISTS audit_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                action TEXT NOT NULL,
                entity_type TEXT,
                entity_id INTEGER,
                metadata TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
            )',
        ];
    }

    /** @return list<string> */
    private static function mysql(): array
    {
        return [
            'CREATE TABLE IF NOT EXISTS settings (
                setting_key VARCHAR(191) PRIMARY KEY,
                setting_value LONGTEXT NOT NULL,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS users (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                email VARCHAR(255) NOT NULL UNIQUE,
                password_hash VARCHAR(255) NOT NULL,
                totp_secret VARCHAR(64) NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS monthly_plans (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL UNIQUE,
                starting_income DECIMAL(14,2) NOT NULL DEFAULT 0,
                rollover_in DECIMAL(14,2) NOT NULL DEFAULT 0,
                rolling_enabled TINYINT(1) NOT NULL DEFAULT 1,
                rollover_source_month CHAR(7) NULL,
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS income_entries (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL,
                label VARCHAR(255) NOT NULL,
                amount DECIMAL(14,2) NOT NULL,
                received_on DATE NOT NULL,
                withholding_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_income_month (month)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS categories (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(191) NOT NULL UNIQUE,
                color VARCHAR(16) NOT NULL DEFAULT "#7c5cff",
                monthly_limit DECIMAL(14,2) NOT NULL DEFAULT 0,
                active TINYINT(1) NOT NULL DEFAULT 1,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS budget_items (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL,
                category_id BIGINT UNSIGNED NULL,
                name VARCHAR(255) NOT NULL,
                planned_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
                due_date DATE NULL,
                recurrence VARCHAR(16) NOT NULL DEFAULT "none",
                series_key VARCHAR(64) NULL,
                status VARCHAR(24) NOT NULL DEFAULT "active",
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_budget_items_month (month),
                UNIQUE KEY idx_budget_items_series_month (series_key, month),
                CONSTRAINT fk_item_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS transactions (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL,
                budget_item_id BIGINT UNSIGNED NULL,
                category_id BIGINT UNSIGNED NULL,
                merchant VARCHAR(255) NOT NULL,
                amount DECIMAL(14,2) NOT NULL,
                transacted_on DATE NOT NULL,
                dead_pull_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_transactions_month (month),
                CONSTRAINT fk_transaction_item FOREIGN KEY (budget_item_id) REFERENCES budget_items(id) ON DELETE SET NULL,
                CONSTRAINT fk_transaction_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS dead_account_entries (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL,
                kind VARCHAR(32) NOT NULL,
                amount DECIMAL(14,2) NOT NULL,
                source_type VARCHAR(32) NULL,
                source_id BIGINT NULL,
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_dead_month (month),
                INDEX idx_dead_source (source_type, source_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS calendar_events (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                month CHAR(7) NOT NULL,
                title VARCHAR(255) NOT NULL,
                event_date DATE NOT NULL,
                event_type VARCHAR(24) NOT NULL DEFAULT "note",
                notes TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS alert_logs (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                budget_item_id BIGINT UNSIGNED NOT NULL,
                alert_date DATE NOT NULL,
                window_days INT NOT NULL,
                recipient VARCHAR(255) NOT NULL,
                status VARCHAR(24) NOT NULL,
                response TEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY alert_unique (budget_item_id, alert_date, window_days, recipient),
                CONSTRAINT fk_alert_item FOREIGN KEY (budget_item_id) REFERENCES budget_items(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS visits (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                route VARCHAR(191) NOT NULL,
                visitor_hash VARCHAR(64) NOT NULL,
                visited_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_visits_date (visited_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            'CREATE TABLE IF NOT EXISTS audit_logs (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                user_id BIGINT UNSIGNED NULL,
                action VARCHAR(191) NOT NULL,
                entity_type VARCHAR(64) NULL,
                entity_id BIGINT NULL,
                metadata LONGTEXT NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
        ];
    }
}