-- 킨텍스 Phase B(§5B) — 공통/시스템관리 레이어 스키마 (WISE/UIWS TB_* 이식, 멱등)
-- V1~V6 불변. 본 마이그레이션은 순증(additive)만 수행한다.
-- 보안 불변(계약 §0-3): password_hash·otp_secret·시크릿 설정값은 API 응답/로그 노출 금지.

ALTER TABLE app_user ADD COLUMN IF NOT EXISTS otp_enabled   boolean     NOT NULL DEFAULT false;
ALTER TABLE app_user ADD COLUMN IF NOT EXISTS verify_method varchar(10) NOT NULL DEFAULT 'EMAIL';
ALTER TABLE app_user ADD COLUMN IF NOT EXISTS role_code     varchar(20) NOT NULL DEFAULT 'USER';
ALTER TABLE app_user ADD COLUMN IF NOT EXISTS dept_id       varchar(40);
ALTER TABLE app_user ADD COLUMN IF NOT EXISTS last_login_at timestamptz;

CREATE TABLE IF NOT EXISTS common_code_group (
    grp_code    varchar(40)  PRIMARY KEY,
    grp_name    varchar(120) NOT NULL,
    description varchar(400),
    use_yn      char(1)      NOT NULL DEFAULT 'Y',
    sort_order  integer      NOT NULL DEFAULT 0,
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS common_code (
    grp_code    varchar(40)  NOT NULL REFERENCES common_code_group(grp_code) ON DELETE CASCADE,
    code        varchar(80)  NOT NULL,
    code_name   varchar(200) NOT NULL,
    code_value  varchar(400),
    sort_order  integer      NOT NULL DEFAULT 0,
    use_yn      char(1)      NOT NULL DEFAULT 'Y',
    attr1       varchar(200),
    description varchar(400),
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (grp_code, code)
);
CREATE INDEX IF NOT EXISTS idx_common_code_grp ON common_code(grp_code, sort_order);

CREATE TABLE IF NOT EXISTS sys_menu (
    id            varchar(40)  PRIMARY KEY,
    parent_id     varchar(40)  REFERENCES sys_menu(id) ON DELETE CASCADE,
    menu_name     varchar(120) NOT NULL,
    menu_path     varchar(200),
    icon          varchar(60),
    prg_type      varchar(20)  NOT NULL DEFAULT 'FORM',
    required_role varchar(20),
    sort_order    integer      NOT NULL DEFAULT 0,
    use_yn        char(1)      NOT NULL DEFAULT 'Y',
    created_at    timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_sys_menu_parent ON sys_menu(parent_id, sort_order);

CREATE TABLE IF NOT EXISTS sys_role (
    role_code   varchar(20)  PRIMARY KEY,
    role_name   varchar(120) NOT NULL,
    description varchar(400),
    use_yn      char(1)      NOT NULL DEFAULT 'Y',
    created_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS sys_permission (
    perm_code   varchar(60)  PRIMARY KEY,
    perm_name   varchar(120) NOT NULL,
    description varchar(400),
    created_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS sys_role_permission (
    role_code varchar(20) NOT NULL REFERENCES sys_role(role_code) ON DELETE CASCADE,
    perm_code varchar(60) NOT NULL REFERENCES sys_permission(perm_code) ON DELETE CASCADE,
    PRIMARY KEY (role_code, perm_code)
);
CREATE TABLE IF NOT EXISTS sys_setting (
    setting_key   varchar(120) PRIMARY KEY,
    setting_value text,
    value_type    varchar(20)  NOT NULL DEFAULT 'STRING',
    description   varchar(400),
    secret_yn     char(1)      NOT NULL DEFAULT 'N',
    updated_at    timestamptz  NOT NULL DEFAULT now(),
    updated_by    varchar(40)
);
CREATE TABLE IF NOT EXISTS audit_log (
    id              bigserial    PRIMARY KEY,
    actor_id        varchar(40),
    actor_name      varchar(120),
    action          varchar(80)  NOT NULL,
    target_type     varchar(60),
    target_id       varchar(80),
    event_id        varchar(40),
    summary         varchar(1000),
    ruleset_version varchar(40),
    result          varchar(20)  NOT NULL DEFAULT 'SUCCESS',
    ip_hint         varchar(60),
    created_at      timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_log_created ON audit_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_log_actor   ON audit_log(actor_id);
CREATE INDEX IF NOT EXISTS idx_audit_log_event   ON audit_log(event_id);

INSERT INTO sys_role (role_code, role_name, description) VALUES
  ('USER',    '일반 사용자',   '본인 데이터 범위'),
  ('MANAGER', '매니저',        '소속 팀 데이터 범위'),
  ('ADMIN',   '시스템관리자',  '전체 데이터 + 시스템관리(백오피스)')
ON CONFLICT (role_code) DO UPDATE SET role_name = EXCLUDED.role_name, description = EXCLUDED.description;

INSERT INTO sys_permission (perm_code, perm_name, description) VALUES
  ('SYS_USER_MANAGE', '사용자 관리',    '시스템관리 사용자 CRUD'),
  ('ROLE_MANAGE',     '역할/권한 관리', 'RBAC 역할·권한 매핑'),
  ('CODE_MANAGE',     '공통코드 관리',  '코드그룹/코드 CRUD'),
  ('MENU_MANAGE',     '메뉴 관리',      '메뉴 트리 CRUD'),
  ('SETTING_MANAGE',  '시스템설정 관리','설정 조회/변경'),
  ('AUDIT_VIEW',      '감사로그 조회',  '감사 로그 열람')
ON CONFLICT (perm_code) DO UPDATE SET perm_name = EXCLUDED.perm_name;

INSERT INTO sys_role_permission (role_code, perm_code)
SELECT 'ADMIN', perm_code FROM sys_permission
ON CONFLICT DO NOTHING;

INSERT INTO common_code_group (grp_code, grp_name, description, sort_order) VALUES
  ('USE_YN',        '사용여부',        '전 관리화면 공통', 1),
  ('USER_ROLE',     '전역 사용자 역할','USER/MANAGER/ADMIN 데이터 가시범위', 2),
  ('VERIFY_METHOD', '2차검증 방식',    'EMAIL/OTP', 3),
  ('PRG_TYPE',      '프로그램 유형',   'FORM/POPUP', 4),
  ('MSG_RCV_TYPE',  '쪽지 수신구분',   'RECV/REF', 5),
  ('WORK_STATUS',   '업무 진행상태',   'worklog 이식', 6),
  ('WORK_TYPE',     '업무 유형',       'worklog 이식', 7),
  ('WORK_PROGRESS', '업무 진척도',     'worklog 이식', 8),
  ('SCHE_GUBUN',    '일정 구분',       'schedule 이식', 9),
  ('IMPORTANCE',    '중요도',          'schedule/notice', 10),
  ('NOTICE_TYPE',   '공지 분류',       'notice 이식', 11),
  ('OPINION_STATUS','의견 처리상태',   'opinion 이식', 12),
  ('NOTI_TYPE',     '알림 유형',       'notification 이식', 13),
  ('REPORT_TYPE',   '보고서 유형',     'report 이식', 14)
ON CONFLICT (grp_code) DO UPDATE SET grp_name = EXCLUDED.grp_name, description = EXCLUDED.description;

INSERT INTO common_code (grp_code, code, code_name, sort_order) VALUES
  ('USE_YN','Y','사용',1),('USE_YN','N','미사용',2),
  ('USER_ROLE','USER','일반 사용자',1),('USER_ROLE','MANAGER','매니저',2),('USER_ROLE','ADMIN','시스템관리자',3),
  ('VERIFY_METHOD','EMAIL','이메일 인증코드',1),('VERIFY_METHOD','OTP','OTP앱(TOTP)',2),
  ('PRG_TYPE','FORM','폼',1),('PRG_TYPE','POPUP','팝업',2),
  ('MSG_RCV_TYPE','RECV','수신',1),('MSG_RCV_TYPE','REF','참조',2),
  ('WORK_STATUS','TODO','예정',1),('WORK_STATUS','DOING','진행중',2),('WORK_STATUS','DONE','완료',3),('WORK_STATUS','HOLD','보류',4),
  ('WORK_TYPE','OPERATION','운영',1),('WORK_TYPE','SETUP','설치/시공',2),('WORK_TYPE','SUPPORT','지원',3),('WORK_TYPE','ETC','기타',4),
  ('WORK_PROGRESS','P0','0%',1),('WORK_PROGRESS','P25','25%',2),('WORK_PROGRESS','P50','50%',3),('WORK_PROGRESS','P75','75%',4),('WORK_PROGRESS','P100','100%',5),
  ('SCHE_GUBUN','PERSONAL','개인',1),('SCHE_GUBUN','TEAM','팀',2),('SCHE_GUBUN','EVENT','행사',3),
  ('IMPORTANCE','LOW','낮음',1),('IMPORTANCE','NORMAL','보통',2),('IMPORTANCE','HIGH','높음',3),('IMPORTANCE','URGENT','긴급',4),
  ('NOTICE_TYPE','GENERAL','일반',1),('NOTICE_TYPE','SYSTEM','시스템',2),('NOTICE_TYPE','EVENT','행사',3),
  ('OPINION_STATUS','OPEN','접수',1),('OPINION_STATUS','REVIEW','검토중',2),('OPINION_STATUS','ANSWERED','답변완료',3),('OPINION_STATUS','CLOSED','종료',4),
  ('NOTI_TYPE','APPROVAL','승인',1),('NOTI_TYPE','DEADLINE','마감',2),('NOTI_TYPE','MESSAGE','쪽지',3),('NOTI_TYPE','SYSTEM','시스템',4),
  ('REPORT_TYPE','DAILY','일일',1),('REPORT_TYPE','WEEKLY','주간',2),('REPORT_TYPE','MONTHLY','월간',3)
ON CONFLICT (grp_code, code) DO UPDATE SET code_name = EXCLUDED.code_name, sort_order = EXCLUDED.sort_order;

INSERT INTO sys_menu (id, parent_id, menu_name, menu_path, icon, sort_order, required_role) VALUES
  ('m-sys',      NULL,    '시스템관리', NULL,              'settings',  90, 'ADMIN'),
  ('m-sys-user', 'm-sys', '사용자 관리', '/admin/users',    'users',     1,  'ADMIN'),
  ('m-sys-role', 'm-sys', '역할/권한',   '/admin/roles',    'shield',    2,  'ADMIN'),
  ('m-sys-code', 'm-sys', '공통코드',    '/admin/codes',    'list',      3,  'ADMIN'),
  ('m-sys-menu', 'm-sys', '메뉴 관리',   '/admin/menus',    'menu',      4,  'ADMIN'),
  ('m-sys-set',  'm-sys', '시스템설정',  '/admin/settings', 'sliders',   5,  'ADMIN'),
  ('m-sys-audit','m-sys', '감사로그',    '/admin/audit',    'file-text', 6,  'ADMIN'),
  ('m-work',     NULL,    '업무',        NULL,              'briefcase', 10, NULL),
  ('m-work-wl',  'm-work','업무일지',    '/work/worklog',   'edit',      1,  NULL),
  ('m-work-sc',  'm-work','일정',        '/work/schedule',  'calendar',  2,  NULL),
  ('m-work-msg', 'm-work','쪽지',        '/work/message',   'mail',      3,  NULL),
  ('m-work-nt',  'm-work','공지',        '/work/notice',    'bell',      4,  NULL),
  ('m-work-op',  'm-work','의견',        '/work/opinion',   'message-circle', 5, NULL),
  ('m-work-mt',  'm-work','회의',        '/work/meeting',   'video',     6,  NULL),
  ('m-work-rp',  'm-work','보고서',      '/work/report',    'bar-chart', 7,  NULL),
  ('m-work-st',  'm-work','통계',        '/work/stats',     'pie-chart', 8,  NULL)
ON CONFLICT (id) DO UPDATE SET menu_name = EXCLUDED.menu_name, menu_path = EXCLUDED.menu_path,
    icon = EXCLUDED.icon, sort_order = EXCLUDED.sort_order, required_role = EXCLUDED.required_role;

INSERT INTO sys_setting (setting_key, setting_value, value_type, description) VALUES
  ('login.maxFailedAttempts', '5',      'INT',    '로그인 실패 잠금 임계치'),
  ('login.lockMinutes',       '15',     'INT',    '잠금 유지 시간(분)'),
  ('otp.issuer',              'KINTEX', 'STRING', 'TOTP 발급자 표기'),
  ('notice.pinnedLimit',      '5',      'INT',    '상단 고정 공지 최대 수')
ON CONFLICT (setting_key) DO NOTHING;
