-- V50: 결재 워크플로우 (approval_line + approval_line_step + approval_request + approval_history)
--      정산/이의신청/시책/출금 결재선 마스터 + 다단 결재 요청·처리 이력.
--      최대 3단계 결재 (시스템 가정값, approval_line.max_step 으로 외부화).

-- ============================================================
-- 1. approval_line — 결재선 마스터
-- ============================================================

CREATE TABLE approval_line (
    line_id      BIGSERIAL    PRIMARY KEY,
    line_code    VARCHAR(50)  NOT NULL UNIQUE,
    line_name    VARCHAR(100) NOT NULL,
    max_step     INT          NOT NULL DEFAULT 3,
    target_type  VARCHAR(20)  NOT NULL,
    is_active    BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_by   BIGINT,
    updated_at   TIMESTAMP,
    CHECK (target_type IN ('SETTLE','DISPUTE','INCENTIVE','WITHDRAW','COMPLAINT')),
    CHECK (max_step BETWEEN 1 AND 5)
);

COMMENT ON TABLE  approval_line             IS '결재선 마스터 — 업무 유형별 결재 단계 정의';
COMMENT ON COLUMN approval_line.line_code   IS '결재선 코드 (예: SETTLE_DEFAULT)';
COMMENT ON COLUMN approval_line.max_step    IS '최대 결재 단계 수 (1~5, 기본 3)';
COMMENT ON COLUMN approval_line.target_type IS '적용 업무 유형 (SETTLE=정산/DISPUTE=이의/INCENTIVE=시책/WITHDRAW=출금/COMPLAINT=민원)';

-- 기본 결재선 초기 데이터
INSERT INTO approval_line (line_code, line_name, max_step, target_type, is_active) VALUES
    ('SETTLE_DEFAULT',   '정산 기본 결재선',   2, 'SETTLE',    TRUE),
    ('DISPUTE_DEFAULT',  '이의신청 결재선',    2, 'DISPUTE',   TRUE),
    ('INCENTIVE_DEFAULT','시책 결재선',        2, 'INCENTIVE', TRUE),
    ('WITHDRAW_DEFAULT', '출금신청 결재선',    3, 'WITHDRAW',  TRUE),
    ('COMPLAINT_DEFAULT','민원처리 결재선',    2, 'COMPLAINT', TRUE)
ON CONFLICT (line_code) DO NOTHING;

-- ============================================================
-- 2. approval_line_step — 결재선 단계별 역할 정의
-- ============================================================

CREATE TABLE approval_line_step (
    step_id    BIGSERIAL   PRIMARY KEY,
    line_id    BIGINT      NOT NULL REFERENCES approval_line(line_id),
    step_no    INT         NOT NULL,
    role_code  VARCHAR(50) NOT NULL,
    -- 결재 역할 코드 (role 테이블 참조, 느슨한 참조)
    created_at TIMESTAMP   NOT NULL DEFAULT NOW(),
    UNIQUE (line_id, step_no),
    CHECK (step_no >= 1)
);

COMMENT ON TABLE  approval_line_step           IS '결재선 단계 — 결재선별 단계 순서와 결재 역할 매핑';
COMMENT ON COLUMN approval_line_step.step_no   IS '결재 단계 번호 (1부터 순서대로)';
COMMENT ON COLUMN approval_line_step.role_code IS '해당 단계 결재 역할 코드';

-- 기본 결재선 단계 삽입
INSERT INTO approval_line_step (line_id, step_no, role_code)
SELECT l.line_id, s.step_no, s.role_code
FROM approval_line l
JOIN (VALUES
    ('SETTLE_DEFAULT',    1, 'MANAGER'),
    ('SETTLE_DEFAULT',    2, 'ADMIN'),
    ('DISPUTE_DEFAULT',   1, 'MANAGER'),
    ('DISPUTE_DEFAULT',   2, 'ADMIN'),
    ('INCENTIVE_DEFAULT', 1, 'MANAGER'),
    ('INCENTIVE_DEFAULT', 2, 'ADMIN'),
    ('WITHDRAW_DEFAULT',  1, 'MANAGER'),
    ('WITHDRAW_DEFAULT',  2, 'ADMIN'),
    ('WITHDRAW_DEFAULT',  3, 'SUPER_ADMIN'),
    ('COMPLAINT_DEFAULT', 1, 'MANAGER'),
    ('COMPLAINT_DEFAULT', 2, 'ADMIN')
) AS s(line_code, step_no, role_code) ON l.line_code = s.line_code
ON CONFLICT (line_id, step_no) DO NOTHING;

-- ============================================================
-- 3. approval_request — 결재 요청
-- ============================================================

CREATE TABLE approval_request (
    request_id    BIGSERIAL    PRIMARY KEY,
    target_type   VARCHAR(20)  NOT NULL,
    target_id     BIGINT       NOT NULL,
    -- 결재 대상 PK (settle_id / dispute_id 등)
    line_id       BIGINT       NOT NULL REFERENCES approval_line(line_id),
    requested_by  BIGINT       NOT NULL,
    -- 요청자 user_id (users 테이블 참조, 느슨한 참조)
    requested_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    current_step  INT          NOT NULL DEFAULT 1,
    status        VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    completed_at  TIMESTAMP,
    created_at    TIMESTAMP    NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMP,
    CHECK (target_type IN ('SETTLE','DISPUTE','INCENTIVE','WITHDRAW','COMPLAINT')),
    CHECK (status IN ('PENDING','APPROVED','REJECTED','CANCEL'))
);

COMMENT ON TABLE  approval_request             IS '결재 요청 — 업무별 결재 요청 생성 및 진행 상태 관리';
COMMENT ON COLUMN approval_request.target_type IS '결재 대상 업무 유형';
COMMENT ON COLUMN approval_request.target_id   IS '결재 대상 레코드 PK';
COMMENT ON COLUMN approval_request.current_step IS '현재 결재 단계 번호';
COMMENT ON COLUMN approval_request.status      IS 'PENDING=진행중/APPROVED=최종승인/REJECTED=반려/CANCEL=취소';

CREATE INDEX idx_ar_target      ON approval_request(target_type, target_id);
CREATE INDEX idx_ar_status      ON approval_request(status, requested_at);
CREATE INDEX idx_ar_requester   ON approval_request(requested_by, status);

-- ============================================================
-- 4. approval_history — 결재 처리 이력
-- ============================================================

CREATE TABLE approval_history (
    history_id    BIGSERIAL    PRIMARY KEY,
    request_id    BIGINT       NOT NULL REFERENCES approval_request(request_id),
    step_no       INT          NOT NULL,
    approver_id   BIGINT       NOT NULL,
    -- 결재자 user_id (느슨한 참조)
    action        VARCHAR(20)  NOT NULL,
    comment       VARCHAR(1000),
    processed_at  TIMESTAMP    NOT NULL DEFAULT NOW(),
    created_at    TIMESTAMP    NOT NULL DEFAULT NOW(),
    CHECK (action IN ('APPROVE','REJECT','RETURN'))
);

COMMENT ON TABLE  approval_history              IS '결재 처리 이력 — 단계별 결재자 액션 및 코멘트 기록';
COMMENT ON COLUMN approval_history.request_id   IS '결재 요청 FK';
COMMENT ON COLUMN approval_history.step_no      IS '처리 단계 번호';
COMMENT ON COLUMN approval_history.approver_id  IS '결재자 user_id';
COMMENT ON COLUMN approval_history.action       IS 'APPROVE=승인/REJECT=반려/RETURN=반송';
COMMENT ON COLUMN approval_history.comment      IS '결재 코멘트';

CREATE INDEX idx_ah_request  ON approval_history(request_id, step_no);
CREATE INDEX idx_ah_approver ON approval_history(approver_id, processed_at);
