-- ============================================================================
-- UIWS system(시스템관리·권한관리) 이식 (CMS) — com.zioinfo.cms.uiws.system
-- 원본: workspace/uiws/db/02_schema_core.sql (TB_DEPT/COMPANY/CODE_GRP/CODE/USER/ROLE/
--       DEPT_ROLE/PROGRAM/MENU/ROLE_MENU). 멀티테넌트 키 체계(VARCHAR ID)를 그대로 유지.
--
-- 네임스페이스 격리: 원본 TB_* → 소문자 tb_uiws_ 프리픽스. CMS cms_user(BIGSERIAL)/cms_member 와
--   별개 계정 모델(tb_uiws_sys_user: VARCHAR user_id) — 절대 병합하지 않는다.
-- 멱등: 전부 CREATE TABLE IF NOT EXISTS / ON CONFLICT DO NOTHING. mode:always 재실행 완전 멱등.
-- FK 정책: tb_uiws_* 내부 참조만 물리 FK(원본과 동일). 비밀번호는 BCrypt 저장.
-- 보안: 비밀번호/임시비번/자격증명은 응답·로그(코드 외)로 노출하지 않는다(불변규칙).
-- ============================================================================

SET client_encoding = 'UTF8';

-- ───────────────────────────────────────────────────────────────────────────
-- 부서 (자기참조 계층)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_dept (
    dept_id         VARCHAR(20)  NOT NULL,
    dept_nm         VARCHAR(100) NOT NULL,
    parent_dept_id  VARCHAR(20),
    sort_ord        INT          DEFAULT 0,
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_dept PRIMARY KEY (dept_id),
    CONSTRAINT fk_uiws_dept_parent FOREIGN KEY (parent_dept_id) REFERENCES tb_uiws_dept (dept_id),
    CONSTRAINT ck_uiws_dept_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_dept IS 'UIWS 이식: 부서 (2-depth 계층, 자기참조)';

-- ───────────────────────────────────────────────────────────────────────────
-- 거래처(=근무처) 마스터
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_company (
    company_id      VARCHAR(20)  NOT NULL,
    company_nm      VARCHAR(100) NOT NULL,
    biz_no          VARCHAR(20),
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_company PRIMARY KEY (company_id),
    CONSTRAINT ck_uiws_company_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_company IS 'UIWS 이식: 거래처=근무처 마스터';

-- ───────────────────────────────────────────────────────────────────────────
-- 공통코드 그룹 / 값 (복합 PK)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_code_grp (
    grp_cd          VARCHAR(30)  NOT NULL,
    grp_nm          VARCHAR(100) NOT NULL,
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_code_grp PRIMARY KEY (grp_cd),
    CONSTRAINT ck_uiws_code_grp_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_code_grp IS 'UIWS 이식: 공통코드 그룹';

CREATE TABLE IF NOT EXISTS tb_uiws_code (
    grp_cd          VARCHAR(30)  NOT NULL,
    code_val        VARCHAR(30)  NOT NULL,
    code_nm         VARCHAR(100) NOT NULL,
    sort_ord        INT          DEFAULT 0,
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_code PRIMARY KEY (grp_cd, code_val),
    CONSTRAINT fk_uiws_code_grp FOREIGN KEY (grp_cd) REFERENCES tb_uiws_code_grp (grp_cd),
    CONSTRAINT ck_uiws_code_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_code IS 'UIWS 이식: 공통코드 값 (복합 PK: grp_cd + code_val)';

-- ───────────────────────────────────────────────────────────────────────────
-- 사용자(계정) — CMS cms_user 와 별개(VARCHAR user_id, 멀티테넌트 업무 사용자)
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_sys_user (
    user_id         VARCHAR(20)  NOT NULL,
    user_nm         VARCHAR(50)  NOT NULL,
    password        VARCHAR(100) NOT NULL,                  -- BCrypt
    email           VARCHAR(100) NOT NULL,
    grade_cd        VARCHAR(20),
    dept_id         VARCHAR(20),
    company_id      VARCHAR(20),
    role_cd         VARCHAR(20)  NOT NULL DEFAULT 'USER',   -- USER/MANAGER/ADMIN
    naverworks_id   VARCHAR(100),
    login_fail_cnt  INT          NOT NULL DEFAULT 0,
    lock_yn         CHAR(1)      NOT NULL DEFAULT 'N',
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    approval_yn     CHAR(1)      NOT NULL DEFAULT 'N',
    verify_method   VARCHAR(20)  NOT NULL DEFAULT 'EMAIL',
    otp_secret      VARCHAR(100),
    pw_change_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_sys_user PRIMARY KEY (user_id),
    CONSTRAINT fk_uiws_sysuser_dept    FOREIGN KEY (dept_id)    REFERENCES tb_uiws_dept (dept_id),
    CONSTRAINT fk_uiws_sysuser_company FOREIGN KEY (company_id) REFERENCES tb_uiws_company (company_id),
    CONSTRAINT uq_uiws_sysuser_email   UNIQUE (email),
    CONSTRAINT ck_uiws_sysuser_lock_yn     CHECK (lock_yn     IN ('Y','N')),
    CONSTRAINT ck_uiws_sysuser_use_yn      CHECK (use_yn      IN ('Y','N')),
    CONSTRAINT ck_uiws_sysuser_approval_yn CHECK (approval_yn IN ('Y','N')),
    CONSTRAINT ck_uiws_sysuser_verify      CHECK (verify_method IN ('EMAIL','OTP')),
    CONSTRAINT ck_uiws_sysuser_pwchg_yn    CHECK (pw_change_yn IN ('Y','N')),
    CONSTRAINT ck_uiws_sysuser_role_cd     CHECK (role_cd     IN ('USER','MANAGER','ADMIN')),
    CONSTRAINT ck_uiws_sysuser_fail_cnt    CHECK (login_fail_cnt >= 0)
);
COMMENT ON TABLE tb_uiws_sys_user IS 'UIWS 이식: 업무 사용자 계정(BCrypt, 잠금/실패횟수, 가입승인) — CMS cms_user 와 별개';

-- ───────────────────────────────────────────────────────────────────────────
-- 권한(역할) / 부서-권한 매핑
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_role (
    role_id         VARCHAR(20)  NOT NULL,
    role_nm         VARCHAR(100) NOT NULL,
    role_desc       VARCHAR(255),
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_role PRIMARY KEY (role_id),
    CONSTRAINT ck_uiws_role_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_role IS 'UIWS 이식: 권한(역할)';

CREATE TABLE IF NOT EXISTS tb_uiws_dept_role (
    dept_id         VARCHAR(20)  NOT NULL,
    role_id         VARCHAR(20)  NOT NULL,
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_dept_role PRIMARY KEY (dept_id, role_id),
    CONSTRAINT fk_uiws_deptrole_dept FOREIGN KEY (dept_id) REFERENCES tb_uiws_dept (dept_id),
    CONSTRAINT fk_uiws_deptrole_role FOREIGN KEY (role_id) REFERENCES tb_uiws_role (role_id)
);
COMMENT ON TABLE tb_uiws_dept_role IS 'UIWS 이식: 부서-권한 매핑 (복합 PK)';

-- ───────────────────────────────────────────────────────────────────────────
-- 프로그램(화면) / 메뉴 / 권한-메뉴 매핑
-- ───────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tb_uiws_program (
    program_id      VARCHAR(20)  NOT NULL,
    program_nm      VARCHAR(100) NOT NULL,
    program_type    VARCHAR(10)  NOT NULL,
    program_url     VARCHAR(200),
    category        VARCHAR(50),
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_program PRIMARY KEY (program_id),
    CONSTRAINT ck_uiws_program_type   CHECK (program_type IN ('FORM','POPUP')),
    CONSTRAINT ck_uiws_program_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_program IS 'UIWS 이식: 프로그램(화면 FORM/POPUP)';

CREATE TABLE IF NOT EXISTS tb_uiws_menu (
    menu_id         VARCHAR(20)  NOT NULL,
    menu_nm         VARCHAR(100) NOT NULL,
    parent_menu_id  VARCHAR(20),
    program_id      VARCHAR(20),
    menu_url        VARCHAR(200),
    sort_ord        INT          DEFAULT 0,
    use_yn          CHAR(1)      NOT NULL DEFAULT 'Y',
    created_by      VARCHAR(20)  NOT NULL,
    created_at      TIMESTAMP    NOT NULL DEFAULT now(),
    updated_by      VARCHAR(20),
    updated_at      TIMESTAMP,
    CONSTRAINT pk_tb_uiws_menu PRIMARY KEY (menu_id),
    CONSTRAINT fk_uiws_menu_parent  FOREIGN KEY (parent_menu_id) REFERENCES tb_uiws_menu (menu_id),
    CONSTRAINT fk_uiws_menu_program FOREIGN KEY (program_id)     REFERENCES tb_uiws_program (program_id),
    CONSTRAINT ck_uiws_menu_use_yn CHECK (use_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_menu IS 'UIWS 이식: 메뉴 (2-depth 자기참조, 프로그램 연결)';

CREATE TABLE IF NOT EXISTS tb_uiws_role_menu (
    role_id         VARCHAR(20)  NOT NULL,
    menu_id         VARCHAR(20)  NOT NULL,
    read_yn         CHAR(1)      NOT NULL DEFAULT 'Y',
    write_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_role_menu PRIMARY KEY (role_id, menu_id),
    CONSTRAINT fk_uiws_rolemenu_role FOREIGN KEY (role_id) REFERENCES tb_uiws_role (role_id),
    CONSTRAINT fk_uiws_rolemenu_menu FOREIGN KEY (menu_id) REFERENCES tb_uiws_menu (menu_id),
    CONSTRAINT ck_uiws_rolemenu_read_yn  CHECK (read_yn  IN ('Y','N')),
    CONSTRAINT ck_uiws_rolemenu_write_yn CHECK (write_yn IN ('Y','N'))
);
COMMENT ON TABLE tb_uiws_role_menu IS 'UIWS 이식: 권한-메뉴 매핑 (복합 PK, 조회/등록 권한)';

-- 조회 보조 인덱스
CREATE INDEX IF NOT EXISTS ix_uiws_sysuser_dept    ON tb_uiws_sys_user (dept_id);
CREATE INDEX IF NOT EXISTS ix_uiws_sysuser_company ON tb_uiws_sys_user (company_id);
CREATE INDEX IF NOT EXISTS ix_uiws_menu_parent     ON tb_uiws_menu (parent_menu_id, sort_ord);

-- ───────────────────────────────────────────────────────────────────────────
-- 최소 시드(멱등): 기본 권한 + 거래처 + 부서. ON CONFLICT DO NOTHING.
-- ───────────────────────────────────────────────────────────────────────────
INSERT INTO tb_uiws_role (role_id, role_nm, role_desc, use_yn, created_by)
VALUES ('ADMIN', '관리자', '시스템 관리자', 'Y', 'SYSTEM'),
       ('MANAGER', '매니저', '팀 관리자', 'Y', 'SYSTEM'),
       ('USER', '일반사용자', '일반 사용자', 'Y', 'SYSTEM')
ON CONFLICT (role_id) DO NOTHING;

INSERT INTO tb_uiws_company (company_id, company_nm, use_yn, created_by)
VALUES ('ZIOINFO', '지오정보기술', 'Y', 'SYSTEM')
ON CONFLICT (company_id) DO NOTHING;

INSERT INTO tb_uiws_dept (dept_id, dept_nm, parent_dept_id, sort_ord, use_yn, created_by)
VALUES ('ROOT', '본사', NULL, 0, 'Y', 'SYSTEM')
ON CONFLICT (dept_id) DO NOTHING;

-- end 92_uiws_system.sql
