-- 002_plan.sql — tabel perencanaan budget (WP-0B)

CREATE TABLE budget_version (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    year SMALLINT NOT NULL,
    label VARCHAR(40) NOT NULL,
    status ENUM('draft','submitted','approved','locked') NOT NULL DEFAULT 'draft',
    locked_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE budget_line (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    department_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    scenario ENUM('LY_ACTUAL','TY_ACTUAL','TY_ESTIMATE','TY_BUDGET','NY_BUDGET') NOT NULL,
    m01 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m02 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m03 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m04 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m05 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m06 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m07 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m08 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m09 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m10 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m11 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m12 DECIMAL(18,2) NOT NULL DEFAULT 0,
    has_detail TINYINT(1) NOT NULL DEFAULT 0,
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_budget_line (version_id, department_id, account_id, scenario),
    KEY idx_budget_line_department (department_id),
    KEY idx_budget_line_account (account_id),
    CONSTRAINT fk_budget_line_version FOREIGN KEY (version_id) REFERENCES budget_version (id) ON DELETE RESTRICT,
    CONSTRAINT fk_budget_line_department FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE RESTRICT,
    CONSTRAINT fk_budget_line_account FOREIGN KEY (account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE budget_line_detail (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    budget_line_id INT UNSIGNED NOT NULL,
    item_name VARCHAR(150) NOT NULL,
    note VARCHAR(255) NULL,
    m01 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m02 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m03 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m04 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m05 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m06 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m07 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m08 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m09 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m10 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m11 DECIMAL(18,2) NOT NULL DEFAULT 0,
    m12 DECIMAL(18,2) NOT NULL DEFAULT 0,
    sort_order INT NULL,
    PRIMARY KEY (id),
    KEY idx_budget_line_detail_line (budget_line_id),
    CONSTRAINT fk_budget_line_detail_line FOREIGN KEY (budget_line_id) REFERENCES budget_line (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE driver_value (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    driver_code VARCHAR(40) NOT NULL,
    ref_code VARCHAR(40) NULL,
    month TINYINT NOT NULL,
    value DECIMAL(18,6) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_driver_value (version_id, driver_code, ref_code, month),
    CONSTRAINT fk_driver_value_version FOREIGN KEY (version_id) REFERENCES budget_version (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE scenario (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(60) NOT NULL,
    params_json JSON NOT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;