-- ============================================================
-- quoERP — Presupuestos anuales
-- Migracion: 029_budgets.sql
-- ============================================================
-- Permite definir un presupuesto anual por cuenta contable
-- (ingresos y gastos) con distribucion mensual opcional.
-- El reporte compara lo presupuestado vs lo real (asientos posted).
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Encabezado del presupuesto
CREATE TABLE IF NOT EXISTS budgets (
    id          INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    company_id  INT UNSIGNED   NOT NULL,
    year        SMALLINT UNSIGNED NOT NULL,
    name        VARCHAR(100)   NOT NULL DEFAULT 'Presupuesto',
    status      ENUM('draft','active','closed') NOT NULL DEFAULT 'draft',
    notes       TEXT           DEFAULT NULL,
    created_by  INT UNSIGNED   DEFAULT NULL,
    created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_company_year (company_id, year),
    INDEX idx_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lineas: monto presupuestado por cuenta y mes
CREATE TABLE IF NOT EXISTS budget_lines (
    id         INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    budget_id  INT UNSIGNED    NOT NULL,
    account_id INT UNSIGNED    NOT NULL,
    -- Montos mensuales (0 si no se presupuesta ese mes)
    jan        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    feb        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    mar        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    apr        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    may        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    jun        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    jul        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    aug        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    sep        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    oct        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    nov        DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `dec`      DECIMAL(14,2)   NOT NULL DEFAULT 0,
    annual_total DECIMAL(16,2) GENERATED ALWAYS AS
                (jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+`dec`) STORED,
    PRIMARY KEY (id),
    UNIQUE KEY uk_budget_account (budget_id, account_id),
    INDEX idx_budget  (budget_id),
    INDEX idx_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;