-- 月账 · Monthly Ledger — full schema (fresh install)
-- Phase 1 created: users, categories, payment_methods, expenses, settings.
-- Phase 2 added: auth_tokens, plus brute-force lockout columns on users.
-- Phase 8 added: budgets.
-- Phase 9 added: incomes.
-- This file is updated to match at each phase so it always represents a
-- complete, current, fresh-install schema.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- users — the two account holders. No public registration; rows are created
-- only by seed.sql / a future admin action.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id                     INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username               VARCHAR(50)  NOT NULL,
    display_name           VARCHAR(100) NOT NULL,
    password_hash          VARCHAR(255) NOT NULL,
    is_admin               TINYINT(1)   NOT NULL DEFAULT 0,
    failed_login_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until           DATETIME     NULL,
    created_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at          DATETIME     NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- auth_tokens — powers "stay signed in" (Phase 2). Split selector/validator
-- design: only a hash of the validator is stored, so a stolen database row
-- alone can't be replayed as a cookie.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_tokens (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     INT UNSIGNED NOT NULL,
    selector    CHAR(12) NOT NULL,
    token_hash  CHAR(64) NOT NULL,
    expires_at  DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_auth_tokens_selector (selector),
    KEY idx_auth_tokens_user (user_id),
    CONSTRAINT fk_auth_tokens_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- categories — shared by expenses and (from Phase 9) income, split by `type`.
-- Rows are never deleted, only deactivated, so historical entries keep their
-- labels forever.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(50)  NOT NULL,
    type        ENUM('expense', 'income') NOT NULL,
    color       CHAR(7)      NOT NULL,
    sort_order  SMALLINT     NOT NULL DEFAULT 0,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_type_name (type, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- payment_methods — same lifecycle rules as categories (deactivate, never
-- delete).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payment_methods (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(50)  NOT NULL,
    sort_order  SMALLINT     NOT NULL DEFAULT 0,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_payment_methods_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- expenses — the heart of the system. A "month" is never stored; it is only
-- ever a filter on expense_date. This is what removes the old Excel ritual
-- of creating a new file every month.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS expenses (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    expense_date        DATE           NOT NULL,
    amount              DECIMAL(10,2)  NOT NULL,
    category_id         INT UNSIGNED   NOT NULL,
    payment_method_id   INT UNSIGNED   NULL,
    user_id             INT UNSIGNED   NOT NULL,
    description         VARCHAR(255)   NULL,
    created_at          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_expenses_date (expense_date),
    KEY idx_expenses_category (category_id),
    KEY idx_expenses_user (user_id),
    KEY idx_expenses_payment_method (payment_method_id),
    CONSTRAINT fk_expenses_category
        FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT,
    CONSTRAINT fk_expenses_payment_method
        FOREIGN KEY (payment_method_id) REFERENCES payment_methods (id) ON DELETE RESTRICT,
    CONSTRAINT fk_expenses_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- settings — simple key/value store (currency symbol/position, etc.).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    setting_key    VARCHAR(50)  NOT NULL,
    setting_value  VARCHAR(255) NOT NULL,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- budgets — planned amount per category per month (Phase 8). One row per
-- (category, year, month); first visit to an empty month copies the
-- previous month's rows forward (includes/helpers.php, idempotent).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS budgets (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    category_id     INT UNSIGNED  NOT NULL,
    budget_year     SMALLINT      NOT NULL,
    budget_month    TINYINT       NOT NULL,
    planned_amount  DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_budgets_category_month (category_id, budget_year, budget_month),
    CONSTRAINT fk_budgets_category
        FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- incomes — identical shape to expenses minus payment_method_id (Phase 9).
-- category_id points at type='income' categories (Paycheck, Bonus, ...).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS incomes (
    id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    income_date   DATE          NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    category_id   INT UNSIGNED  NOT NULL,
    user_id       INT UNSIGNED  NOT NULL,
    description   VARCHAR(255)  NULL,
    created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_incomes_date (income_date),
    KEY idx_incomes_category (category_id),
    KEY idx_incomes_user (user_id),
    CONSTRAINT fk_incomes_category
        FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT,
    CONSTRAINT fk_incomes_user
        FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- visitor_log — plain request/visit tracking, not tied to a logged-in user
-- (page views can happen pre-login too, e.g. login.php itself). Admin-only
-- log_dashboard.php reads this. No foreign keys — it must never fail to
-- insert just because of an unrelated data issue elsewhere.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS visitor_log (
    id          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    ip          VARCHAR(45)   NOT NULL,
    country     VARCHAR(64)   NULL,
    region      VARCHAR(64)   NULL,
    city        VARCHAR(64)   NULL,
    latitude    DECIMAL(9,6)  NULL,
    longitude   DECIMAL(9,6)  NULL,
    isp         VARCHAR(128)  NULL,
    page        VARCHAR(512)  NOT NULL,
    referrer    VARCHAR(512)  NULL,
    user_agent  VARCHAR(512)  NULL,
    browser     VARCHAR(64)   NULL,
    os          VARCHAR(64)   NULL,
    device      VARCHAR(32)   NULL,
    language    VARCHAR(32)   NULL,
    visited_at  DATETIME      NOT NULL,
    PRIMARY KEY (id),
    KEY idx_visitor_log_ip (ip),
    KEY idx_visitor_log_country (country),
    KEY idx_visitor_log_visited_at (visited_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
