-- 003_user.sql — tabel user & workflow (WP-0B)

CREATE TABLE app_user (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(40) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(80) NOT NULL,
    role ENUM('dept_user','hc_user','admin','viewer') NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_app_user_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_department (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    department_id INT UNSIGNED NOT NULL,
    can_edit TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_user_department (user_id, department_id),
    KEY idx_user_department_department (department_id),
    CONSTRAINT fk_user_department_user FOREIGN KEY (user_id) REFERENCES app_user (id) ON DELETE RESTRICT,
    CONSTRAINT fk_user_department_department FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dept_status (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    department_id INT UNSIGNED NOT NULL,
    status ENUM('draft','submitted','approved') NOT NULL DEFAULT 'draft',
    submitted_at DATETIME NULL,
    approved_by INT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_dept_status (version_id, department_id),
    KEY idx_dept_status_department (department_id),
    CONSTRAINT fk_dept_status_version FOREIGN KEY (version_id) REFERENCES budget_version (id) ON DELETE RESTRICT,
    CONSTRAINT fk_dept_status_department FOREIGN KEY (department_id) REFERENCES department (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NULL,
    entity VARCHAR(40) NOT NULL,
    entity_id VARCHAR(40) NOT NULL,
    field VARCHAR(60) NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_log_entity (entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;