-- ===========================================================================
-- Migration: Budgets
-- Date: 2026-07-29
--
-- An annual planned amount per income/expense account. The Budgets screen
-- compares these against what the ledger actually posted for the year (actuals
-- read straight from journal_entry_lines, so budget-vs-actual ties out to the
-- P&L). One row per (farm, account, fiscal_year). Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS budgets (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id     INT UNSIGNED  NOT NULL,
  account_id  INT UNSIGNED  NOT NULL,
  fiscal_year SMALLINT UNSIGNED NOT NULL,
  amount      DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_budget (farm_id, account_id, fiscal_year),
  KEY idx_budget_farm_year (farm_id, fiscal_year),
  CONSTRAINT fk_budget_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_budget_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB;