-- V7: 배치 / 대사 / 감사 / 사용자

CREATE TABLE batch_job_log (
    job_id          SERIAL PRIMARY KEY,
    job_name        VARCHAR(100) NOT NULL,
    job_params      TEXT,
    settle_month    CHAR(6),
    status          VARCHAR(20)  NOT NULL DEFAULT 'STARTED',
    current_step    VARCHAR(50),
    progress_pct    INT          DEFAULT 0,
    total_count     INT,
    processed_count INT,
    error_count     INT          DEFAULT 0,
    error_message   TEXT,
    started_at      TIMESTAMP    NOT NULL DEFAULT NOW(),
    finished_at     TIMESTAMP,
    duration_sec    INT,
    triggered_by    BIGINT
);
CREATE INDEX idx_bjl_lookup ON batch_job_log(job_name, started_at);

CREATE TABLE reconciliation (
    recon_id           SERIAL PRIMARY KEY,
    company_code       VARCHAR(20),
    settle_month       CHAR(6)         NOT NULL,
    company_total      DECIMAL(15,2),
    system_total       DECIMAL(15,2),
    diff_amount        DECIMAL(15,2),
    diff_count         INT,
    status             VARCHAR(20)     NOT NULL DEFAULT 'PENDING',
    resolve_note       VARCHAR(500),
    resolved_by        BIGINT,
    resolved_at        TIMESTAMP,
    created_at         TIMESTAMP       NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_recon ON reconciliation(company_code, settle_month);

CREATE TABLE audit_log (
    audit_id      SERIAL PRIMARY KEY,
    user_id       BIGINT,
    action_type   VARCHAR(30) NOT NULL,
    target_type   VARCHAR(50),
    target_id     VARCHAR(100),
    description   VARCHAR(500),
    ip_address    VARCHAR(50),
    created_at    TIMESTAMP   NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_audit_user ON audit_log(user_id, created_at);

CREATE TABLE users (
    user_id          SERIAL PRIMARY KEY,
    login_id         VARCHAR(50)  NOT NULL UNIQUE,
    password         VARCHAR(200) NOT NULL,
    user_name        VARCHAR(50)  NOT NULL,
    email            VARCHAR(100),
    phone            VARCHAR(20),
    agent_id         BIGINT REFERENCES agent(agent_id),
    status           VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',  -- ACTIVE / LOCKED / RETIRED
    fail_count       INT          NOT NULL DEFAULT 0,
    last_login_at    TIMESTAMP,
    pwd_changed_at   TIMESTAMP,
    created_at       TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_by       BIGINT,
    updated_at       TIMESTAMP,
    updated_by       BIGINT
);
CREATE INDEX idx_users_status ON users(status);
