-- Phase 8 — Monthly Budgets
-- Run against a database that already has migrations 001 (schema.sql) and
-- 002_auth_tokens.sql applied. Adds the budgets table only — additive.

SET FOREIGN_KEY_CHECKS = 0;

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;

SET FOREIGN_KEY_CHECKS = 1;
