-- 001_master.sql — tabel master (WP-0B)

CREATE TABLE department (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(60) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_department_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ftype (
    id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    weight INT NULL,
    weight2 INT NULL,
    level1 VARCHAR(40) NULL,
    level2 VARCHAR(60) NULL,
    normal_balance ENUM('D-K','K-D') NULL,
    report ENUM('PL','BS') NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE coa_account (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(150) NOT NULL,
    name_nondiv VARCHAR(150) NULL,
    ftype_id INT UNSIGNED NULL,
    tipe_biaya VARCHAR(20) NULL,
    divisi VARCHAR(20) NULL,
    is_fixed TINYINT(1) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_coa_account_code (code),
    KEY idx_coa_account_ftype (ftype_id),
    CONSTRAINT fk_coa_account_ftype FOREIGN KEY (ftype_id) REFERENCES ftype (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE department_account (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    section VARCHAR(30) NULL,
    sort_order INT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_department_account (department_id, account_id),
    KEY idx_department_account_account (account_id),
    CONSTRAINT fk_department_account_department FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE RESTRICT,
    CONSTRAINT fk_department_account_account FOREIGN KEY (account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE param_global (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NULL,
    param_code VARCHAR(40) NOT NULL,
    param_value DECIMAL(18,6) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_param_global (version_id, param_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_line (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_code VARCHAR(20) NOT NULL,
    line_no INT NOT NULL,
    label VARCHAR(120) NOT NULL,
    line_type VARCHAR(20) NOT NULL,
    source_expr VARCHAR(255) NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE form_definition (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id INT UNSIGNED NULL,
    form_code VARCHAR(30) NOT NULL,
    form_type ENUM('grid','driver') NOT NULL,
    title VARCHAR(100) NOT NULL,
    config_json JSON NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_form_definition_code (form_code),
    KEY idx_form_definition_department (department_id),
    CONSTRAINT fk_form_definition_department FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE formula_registry (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    formula_code VARCHAR(40) NOT NULL,
    class_name VARCHAR(120) NOT NULL,
    calc_order INT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_formula_registry_code (formula_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE insight_rule (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_code VARCHAR(40) NOT NULL,
    title VARCHAR(120) NOT NULL,
    condition_expr TEXT NOT NULL,
    severity TINYINT NOT NULL,
    template_text TEXT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_insight_rule_code (rule_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE insight_playbook (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    rule_id INT UNSIGNED NOT NULL,
    title VARCHAR(120) NOT NULL,
    action_text TEXT NOT NULL,
    impact_expr VARCHAR(255) NULL,
    sort_order INT NULL,
    PRIMARY KEY (id),
    KEY idx_insight_playbook_rule (rule_id),
    CONSTRAINT fk_insight_playbook_rule FOREIGN KEY (rule_id) REFERENCES insight_rule (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;