-- ============================================================================
-- UIWS 업무 테이블 이식 (MES, ERP 파일럿 복제·치환) — 2026-06-20, uiws-schema-porter
-- 계획서: .claude/agents/_workspace/uiws_port_plan.md (schema-porter 트랙 6.1)
-- 원본: workspace/uiws/db/{03_worklog,04_schedule,05_message,02_core(2FA)}.sql
--
-- 네임스페이스 격리: UIWS TB_* → 소문자 tb_uiws_ 프리픽스 (기존 tb_audit_log 등과 충돌 회피).
-- 멱등: 전부 CREATE TABLE IF NOT EXISTS / ADD COLUMN IF NOT EXISTS / ON CONFLICT DO NOTHING.
--       mode:always 재실행 시 완전 멱등(기존 schema.sql·시드 무영향).
-- FK 정책: tb_uiws_* 내부 참조만 물리 FK. 외부 user/code/company/dept FK는 논리참조로 완화
--          (UIWS는 VARCHAR USER_ID 키 / ERP user PK는 BIGSERIAL — 키 체계 불일치 → 컬럼만 유지).
-- 코드값(WORK_STATUS_CD/WORK_TYPE_CD/RCV_TYPE 등): TB_CODE 미이식 → 일반 컬럼 + CHECK만 유지.
-- ============================================================================

SET client_encoding = 'UTF8';

-- ───────────────────────────────────────────────────────────────────────────
-- [worklog] tb_uiws_worklog — 업무일지 헤더 (원본 TB_WORKLOG)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_worklog (
    worklog_id         BIGINT       GENERATED ALWAYS AS IDENTITY,
    title              VARCHAR(200) NOT NULL,
    writer_id          VARCHAR(20)  NOT NULL,                 -- 논리참조(UIWS USER_ID)
    work_date          DATE         NOT NULL,
    work_status_cd     VARCHAR(30)  NOT NULL,                 -- WORK_STATUS 코드값(논리)
    progress_cd        VARCHAR(30)  NOT NULL DEFAULT 'ONGOING',
    repeat_yn          CHAR(1)      NOT NULL DEFAULT 'N',
    repeat_start_date  DATE,
    repeat_end_date    DATE,
    created_by         VARCHAR(20)  NOT NULL,
    created_at         TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by         VARCHAR(20),
    updated_at         TIMESTAMP,
    CONSTRAINT pk_tb_uiws_worklog PRIMARY KEY (worklog_id),
    CONSTRAINT ck_uiws_worklog_progress  CHECK (progress_cd IN ('ONGOING','DONE')),
    CONSTRAINT ck_uiws_worklog_repeat_yn CHECK (repeat_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_worklog IS 'UIWS 이식: 업무일지 헤더 (근무일자/근무상태/반복)';

-- tb_uiws_worklog_dtl — 시간대별 상세 (통계 집계 원천, 원본 TB_WORKLOG_DTL)
CREATE TABLE IF NOT EXISTS tb_uiws_worklog_dtl (
    dtl_id          BIGINT       GENERATED ALWAYS AS IDENTITY,
    worklog_id      BIGINT       NOT NULL,
    start_hour      INT          NOT NULL,
    end_hour        INT          NOT NULL,
    work_type_cd    VARCHAR(30)  NOT NULL,                    -- WORK_TYPE 코드값(논리)
    company_id      VARCHAR(20)  NOT NULL,                    -- 논리참조(UIWS COMPANY_ID)
    work_content    TEXT,
    issue_content   TEXT,
    sort_ord        INT          DEFAULT 0,
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_worklog_dtl PRIMARY KEY (dtl_id),
    CONSTRAINT fk_uiws_dtl_worklog FOREIGN KEY (worklog_id)
        REFERENCES tb_uiws_worklog (worklog_id) ON DELETE CASCADE,
    CONSTRAINT ck_uiws_dtl_start_hour CHECK (start_hour BETWEEN 0 AND 24),
    CONSTRAINT ck_uiws_dtl_end_hour   CHECK (end_hour   BETWEEN 0 AND 24),
    CONSTRAINT ck_uiws_dtl_hour_order CHECK (end_hour >= start_hour)
);
COMMENT ON TABLE tb_uiws_worklog_dtl IS 'UIWS 이식: 업무일지 시간대별 상세 (헤더 삭제 시 CASCADE)';

-- tb_uiws_worklog_cmt — 댓글 (원본 TB_WORKLOG_CMT)
CREATE TABLE IF NOT EXISTS tb_uiws_worklog_cmt (
    cmt_id          BIGINT       GENERATED ALWAYS AS IDENTITY,
    worklog_id      BIGINT       NOT NULL,
    cmt_content     TEXT         NOT NULL,
    writer_id       VARCHAR(20)  NOT NULL,                    -- 논리참조
    kakao_sent_yn   CHAR(1)      NOT NULL DEFAULT 'N',
    confirm_yn      CHAR(1)      NOT NULL DEFAULT 'N',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_worklog_cmt PRIMARY KEY (cmt_id),
    CONSTRAINT fk_uiws_cmt_worklog FOREIGN KEY (worklog_id)
        REFERENCES tb_uiws_worklog (worklog_id) ON DELETE CASCADE,
    CONSTRAINT ck_uiws_cmt_confirm_yn CHECK (confirm_yn IN ('Y','N')),
    CONSTRAINT ck_uiws_cmt_kakao_yn   CHECK (kakao_sent_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_worklog_cmt IS 'UIWS 이식: 업무일지 댓글 (등록 시 카카오 알림톡)';

CREATE INDEX IF NOT EXISTS ix_uiws_worklog_date_writer ON tb_uiws_worklog (work_date, writer_id, work_status_cd);
CREATE INDEX IF NOT EXISTS ix_uiws_worklog_writer      ON tb_uiws_worklog (writer_id);
CREATE INDEX IF NOT EXISTS ix_uiws_dtl_type_company    ON tb_uiws_worklog_dtl (work_type_cd, company_id);
CREATE INDEX IF NOT EXISTS ix_uiws_dtl_worklog         ON tb_uiws_worklog_dtl (worklog_id);
CREATE INDEX IF NOT EXISTS ix_uiws_dtl_company         ON tb_uiws_worklog_dtl (company_id);
CREATE INDEX IF NOT EXISTS ix_uiws_cmt_worklog         ON tb_uiws_worklog_cmt (worklog_id);

-- ───────────────────────────────────────────────────────────────────────────
-- [schedule] tb_uiws_schedule — 일정 개인/부서 (원본 TB_SCHEDULE)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_schedule (
    schedule_id     BIGINT       GENERATED ALWAYS AS IDENTITY,
    sche_type       VARCHAR(10)  NOT NULL,
    title           VARCHAR(200) NOT NULL,
    sche_gubun_cd   VARCHAR(30),
    importance_cd   VARCHAR(30),
    start_dt        TIMESTAMP    NOT NULL,
    end_dt          TIMESTAMP    NOT NULL,
    content         TEXT,
    owner_id        VARCHAR(20)  NOT NULL,                    -- 논리참조
    dept_id         VARCHAR(20),                              -- 논리참조
    charger_id      VARCHAR(20),                              -- 논리참조
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_schedule PRIMARY KEY (schedule_id),
    CONSTRAINT ck_uiws_sche_type     CHECK (sche_type IN ('PERSONAL','DEPT')),
    CONSTRAINT ck_uiws_sche_dt_order CHECK (end_dt >= start_dt)
);
COMMENT ON TABLE tb_uiws_schedule IS 'UIWS 이식: 일정(개인 PERSONAL / 부서 DEPT)';

-- tb_uiws_diary — 일지(일정 연계 선택, 원본 TB_DIARY)
CREATE TABLE IF NOT EXISTS tb_uiws_diary (
    diary_id        BIGINT       GENERATED ALWAYS AS IDENTITY,
    title           VARCHAR(200) NOT NULL,
    content         TEXT,
    schedule_id     BIGINT,
    writer_id       VARCHAR(20)  NOT NULL,                    -- 논리참조
    diary_date      DATE,
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_diary PRIMARY KEY (diary_id),
    CONSTRAINT fk_uiws_diary_sche FOREIGN KEY (schedule_id)
        REFERENCES tb_uiws_schedule (schedule_id) ON DELETE SET NULL
);
COMMENT ON TABLE tb_uiws_diary IS 'UIWS 이식: 일지 (일정 연계 선택, 일정 삭제 시 연계 해제)';

-- tb_uiws_attach — 첨부파일 폴리모픽 (원본 TB_ATTACH, 물리 FK 미적용)
CREATE TABLE IF NOT EXISTS tb_uiws_attach (
    attach_id       BIGINT       GENERATED ALWAYS AS IDENTITY,
    ref_type        VARCHAR(20)  NOT NULL,
    ref_id          BIGINT       NOT NULL,
    file_nm         VARCHAR(255) NOT NULL,
    file_path       VARCHAR(500) NOT NULL,
    file_size       BIGINT,
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_attach PRIMARY KEY (attach_id),
    CONSTRAINT ck_uiws_attach_ref_type CHECK (ref_type IN ('SCHEDULE','DIARY')),
    CONSTRAINT ck_uiws_attach_size     CHECK (file_size IS NULL OR file_size >= 0)
);
COMMENT ON TABLE tb_uiws_attach IS 'UIWS 이식: 첨부파일 (폴리모픽 REF_TYPE=SCHEDULE/DIARY)';

CREATE INDEX IF NOT EXISTS ix_uiws_sche_dt_range ON tb_uiws_schedule (start_dt, end_dt, sche_type);
CREATE INDEX IF NOT EXISTS ix_uiws_sche_owner    ON tb_uiws_schedule (owner_id);
CREATE INDEX IF NOT EXISTS ix_uiws_sche_dept     ON tb_uiws_schedule (dept_id);
CREATE INDEX IF NOT EXISTS ix_uiws_diary_sche    ON tb_uiws_diary (schedule_id);
CREATE INDEX IF NOT EXISTS ix_uiws_diary_writer  ON tb_uiws_diary (writer_id, diary_date);
CREATE INDEX IF NOT EXISTS ix_uiws_attach_ref    ON tb_uiws_attach (ref_type, ref_id);

-- ───────────────────────────────────────────────────────────────────────────
-- [message] tb_uiws_message — 쪽지 헤더 (원본 TB_MESSAGE)
--   REF_WORKLOG_ID → tb_uiws_worklog (내부 FK 유지), REPLY_TO_ID → 자기참조.
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_message (
    message_id      BIGINT       GENERATED ALWAYS AS IDENTITY,
    sender_id       VARCHAR(20)  NOT NULL,                    -- 논리참조
    title           VARCHAR(200) NOT NULL,
    content         TEXT         NOT NULL,
    ref_worklog_id  BIGINT,
    reply_to_id     BIGINT,
    sent_at         TIMESTAMP    NOT NULL DEFAULT now(),
    sender_del_yn   CHAR(1)      NOT NULL DEFAULT 'N',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_message PRIMARY KEY (message_id),
    CONSTRAINT fk_uiws_msg_worklog FOREIGN KEY (ref_worklog_id)
        REFERENCES tb_uiws_worklog (worklog_id) ON DELETE SET NULL,
    CONSTRAINT fk_uiws_msg_reply FOREIGN KEY (reply_to_id)
        REFERENCES tb_uiws_message (message_id),
    CONSTRAINT ck_uiws_msg_sender_del_yn CHECK (sender_del_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_message IS 'UIWS 이식: 쪽지 헤더 (참조 업무일지/답장 원본 자기참조)';

-- tb_uiws_message_rcv — 수신자 (원본 TB_MESSAGE_RCV)
CREATE TABLE IF NOT EXISTS tb_uiws_message_rcv (
    rcv_id          BIGINT       GENERATED ALWAYS AS IDENTITY,
    message_id      BIGINT       NOT NULL,
    receiver_id     VARCHAR(20)  NOT NULL,                    -- 논리참조
    rcv_type        VARCHAR(10)  NOT NULL,                    -- MSG_RCV_TYPE 코드값(논리)
    read_yn         CHAR(1)      NOT NULL DEFAULT 'N',
    read_at         TIMESTAMP,
    receiver_del_yn CHAR(1)      NOT NULL DEFAULT 'N',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_message_rcv PRIMARY KEY (rcv_id),
    CONSTRAINT fk_uiws_rcv_message FOREIGN KEY (message_id)
        REFERENCES tb_uiws_message (message_id) ON DELETE CASCADE,
    CONSTRAINT uq_uiws_rcv_msg_receiver UNIQUE (message_id, receiver_id),
    CONSTRAINT ck_uiws_rcv_type    CHECK (rcv_type IN ('RECV','REF')),
    CONSTRAINT ck_uiws_rcv_read_yn CHECK (read_yn IN ('Y','N')),
    CONSTRAINT ck_uiws_rcv_del_yn  CHECK (receiver_del_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_message_rcv IS 'UIWS 이식: 쪽지 수신자(수신 RECV/참조 REF, 개봉여부)';

CREATE INDEX IF NOT EXISTS ix_uiws_rcv_receiver_read ON tb_uiws_message_rcv (receiver_id, read_yn);
CREATE INDEX IF NOT EXISTS ix_uiws_rcv_message       ON tb_uiws_message_rcv (message_id);
CREATE INDEX IF NOT EXISTS ix_uiws_msg_sender        ON tb_uiws_message (sender_id, sent_at);

-- ───────────────────────────────────────────────────────────────────────────
-- [2FA] tb_uiws_login_verify — 로그인 2차 검증 코드 (원본 TB_LOGIN_VERIFY)
--   USER_ID 는 논리참조(외부 user FK 미적용 — 키 체계 불일치).
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_login_verify (
    verify_id       BIGINT       GENERATED ALWAYS AS IDENTITY,
    user_id         VARCHAR(50)  NOT NULL,                    -- MES username 논리참조
    verify_method   VARCHAR(20)  NOT NULL DEFAULT 'EMAIL',
    verify_code     VARCHAR(10)  NOT NULL,
    expire_at       TIMESTAMP    NOT NULL,
    verified_yn     CHAR(1)      NOT NULL DEFAULT 'N',
    created_by      VARCHAR(20)  NOT NULL DEFAULT 'SYSTEM',
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_login_verify PRIMARY KEY (verify_id),
    CONSTRAINT ck_uiws_verify_yn     CHECK (verified_yn IN ('Y','N')),
    CONSTRAINT ck_uiws_verify_method CHECK (verify_method IN ('EMAIL','OTP'))
);
COMMENT ON TABLE tb_uiws_login_verify IS 'UIWS 이식(2FA): 로그인 2차 검증 코드(이메일/OTP, 만료)';
CREATE INDEX IF NOT EXISTS ix_uiws_verify_user ON tb_uiws_login_verify (user_id, verified_yn);

-- ───────────────────────────────────────────────────────────────────────────
-- [2FA] 기존 user 테이블 컬럼 보강 (DROP/재정의 금지 — ADD COLUMN IF NOT EXISTS 멱등)
--   ★ MES mes_user 는 email 컬럼 미보유(원본: username/password_hash/display_name/role/is_active)
--     → 2FA 발송 대상 확보를 위해 email 포함 6컬럼 ALTER(Portal 사례와 동일, ERP는 email 기보유라 5컬럼).
--   이메일·인증코드·만료시각, 실패 카운트(기본 0), 잠금(기본 false), OTP 시크릿.
-- ───────────────────────────────────────────────────────────────────────────
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS email               VARCHAR(255);
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS email_verify_code   VARCHAR(10);
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS email_verify_expire TIMESTAMP;
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS login_fail_count    INT DEFAULT 0;
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS locked              BOOLEAN DEFAULT false;
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS otp_secret          VARCHAR(255);

-- [로그인 보조 이식] 회원가입 승인 게이트 컬럼. 기존 계정은 기본 true(승인됨) → 로그인 회귀 0.
--   신규 가입자만 signup INSERT 시 approved=false 로 등록되어 SUPERADMIN 승인 전 차단.
ALTER TABLE mes_user ADD COLUMN IF NOT EXISTS approved            BOOLEAN DEFAULT true;

-- ───────────────────────────────────────────────────────────────────────────
-- [메뉴] MES 는 DB 메뉴/RBAC 테이블 부재(NAV는 프론트 정적 정의) → SQL 메뉴 시드 대상 없음.
--   "업무 (UIWS)" 메뉴는 프론트 Sidebar/Route 에 추가(코드). 여기서는 주석으로만 명시.
-- ───────────────────────────────────────────────────────────────────────────

-- ───────────────────────────────────────────────────────────────────────────
-- [RAG 기법 토글] 중앙 guardia-rag 최신 AI 기법 적용 토글 (mes_setting 재사용).
--   mes-ai-applier 배선용. 무거운 기법(graphrag·rerank·tool_use·stream)은 서버 RAM 제약상 기본 off.
--   ON CONFLICT DO NOTHING 멱등 — 운영자가 화면에서 변경한 값은 보존(재시드 덮어쓰기 없음).
-- ───────────────────────────────────────────────────────────────────────────
INSERT INTO mes_setting (key, value) VALUES
('rag.enabled',           'true'),
('rag.retrieval_mode',    'vector'),
('rag.rerank',            'false'),
('rag.graphrag',          'false'),
('rag.hybrid',            'false'),
('rag.tool_use',          'false'),
('rag.structured',        'true'),
('rag.stream',            'false'),
('rag.top_k',             '5'),
('rag.max_steps',         '4'),
('rag.temperature',       '0.1'),
('rag.generation_model',  'llama3.2:1b')
ON CONFLICT (key) DO NOTHING;

-- end 91_uiws_port.sql
