-- V37 시스템관리 기본기 이식 1차 (WISE/UIWS system.* 이식 — 소유자 지적 2026-07-12)
-- 목표: 조직(부서/회사)·프로그램·역할-메뉴 매핑·인증정책·로그인이력·오류로그 기본 테이블.
-- 불변: V1~V36 파일 불변. 본 마이그레이션은 순증(additive)·멱등만 수행한다(단일 트랜잭션).
-- 테넌트 표준(V31): 엔티티 테이블은 tenant_id DEFAULT 'KINTEX' + 복합 PK (tenant_id, id) 선두.
--   전역 매핑/설정(role-menu 등 sys_role·sys_menu 참조)은 기존 sys_role_permission 규약과 정합(테넌트 미부여).
-- 보안 불변(§0-3): 비밀번호·시크릿·스택트레이스 미저장. 이메일/IP는 마스킹만 저장.

-- ============================================================================
-- 1) 조직 — 부서(dept) 트리 · 회사(company)
-- ============================================================================
CREATE TABLE IF NOT EXISTS dept (
    tenant_id   varchar(40)  NOT NULL DEFAULT 'KINTEX',
    id          varchar(40)  NOT NULL,
    dept_name   varchar(120) NOT NULL,
    parent_id   varchar(40),
    sort_order  integer      NOT NULL DEFAULT 0,
    use_yn      char(1)      NOT NULL DEFAULT 'Y',
    created_at  timestamptz  NOT NULL DEFAULT now(),
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
);
CREATE INDEX IF NOT EXISTS idx_dept_parent ON dept (tenant_id, parent_id, sort_order);

-- ★company 는 V2 에 이미 존재하는 마스터(공사/등록업체) — 재정의(CREATE)하면 no-op 라 신규 컬럼이 안 생겨
--   후행 인덱스가 없는 컬럼을 참조해 실패한다(2026-07-12 배포 크래시). 기존 테이블을 멱등 ALTER 로 확장한다.
--   PK(id)·기존 컬럼(name·registration_no·phone·registered·category)은 불변 — auction 등 FK 참조 보존.
ALTER TABLE company ADD COLUMN IF NOT EXISTS tenant_id    varchar(40)  NOT NULL DEFAULT 'KINTEX';
ALTER TABLE company ADD COLUMN IF NOT EXISTS company_name varchar(200);
ALTER TABLE company ADD COLUMN IF NOT EXISTS biz_no       varchar(40);
ALTER TABLE company ADD COLUMN IF NOT EXISTS ceo_name     varchar(120);
ALTER TABLE company ADD COLUMN IF NOT EXISTS biz_type     varchar(120);
ALTER TABLE company ADD COLUMN IF NOT EXISTS contact_name varchar(120);
ALTER TABLE company ADD COLUMN IF NOT EXISTS contact_tel  varchar(60);   -- 저장 원문, 응답 마스킹(코드 계층)
ALTER TABLE company ADD COLUMN IF NOT EXISTS address      varchar(400);
ALTER TABLE company ADD COLUMN IF NOT EXISTS partner_type varchar(40);   -- 공통코드(PARTNER_TYPE)
ALTER TABLE company ADD COLUMN IF NOT EXISTS use_yn       char(1)      NOT NULL DEFAULT 'Y';
ALTER TABLE company ADD COLUMN IF NOT EXISTS updated_at   timestamptz  NOT NULL DEFAULT now();
-- V2 기존 컬럼 → W12 컬럼 백필(멱등)
UPDATE company SET company_name = name            WHERE company_name IS NULL AND name IS NOT NULL;
UPDATE company SET biz_no       = registration_no WHERE biz_no IS NULL AND registration_no IS NOT NULL;
UPDATE company SET contact_tel  = phone           WHERE contact_tel IS NULL AND phone IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_company_name ON company (tenant_id, company_name);

-- ============================================================================
-- 2) 프로그램(화면) 등록부 — 메뉴와 연결되는 라우트/화면 마스터
-- ============================================================================
CREATE TABLE IF NOT EXISTS sys_program (
    tenant_id     varchar(40)  NOT NULL DEFAULT 'KINTEX',
    id            varchar(40)  NOT NULL,
    program_name  varchar(160) NOT NULL,
    program_type  varchar(20)  NOT NULL DEFAULT 'SCREEN',  -- SCREEN|POPUP|LINK
    program_url   varchar(300),                            -- 라우트 경로(/admin/users 등)
    category      varchar(80),
    use_yn        char(1)      NOT NULL DEFAULT 'Y',
    created_at    timestamptz  NOT NULL DEFAULT now(),
    updated_at    timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
);
CREATE INDEX IF NOT EXISTS idx_sys_program_cat ON sys_program (tenant_id, category, program_name);

-- ============================================================================
-- 3) 역할-메뉴 매핑 — 역할별 메뉴 접근(read/write). sys_role·sys_menu 참조(전역 규약).
-- ============================================================================
CREATE TABLE IF NOT EXISTS sys_role_menu (
    role_code  varchar(20)  NOT NULL REFERENCES sys_role(role_code) ON DELETE CASCADE,
    menu_id    varchar(40)  NOT NULL REFERENCES sys_menu(id) ON DELETE CASCADE,
    read_yn    char(1)      NOT NULL DEFAULT 'Y',
    write_yn   char(1)      NOT NULL DEFAULT 'N',
    PRIMARY KEY (role_code, menu_id)
);
CREATE INDEX IF NOT EXISTS idx_sys_role_menu_role ON sys_role_menu (role_code);

-- ============================================================================
-- 4) 인증 정책 — 비밀번호 정책·로그인 잠금 임계(테넌트 싱글턴 1행)
--    기존 LoginAttemptService/sys_setting(login.*) 값을 정책화(코드 계층은 sys_setting 유지·본 표는 관리 조회).
-- ============================================================================
CREATE TABLE IF NOT EXISTS sys_auth_policy (
    tenant_id           varchar(40) NOT NULL DEFAULT 'KINTEX',
    pw_min_length       integer     NOT NULL DEFAULT 8,
    pw_require_upper     char(1)     NOT NULL DEFAULT 'N',
    pw_require_number    char(1)     NOT NULL DEFAULT 'Y',
    pw_require_special   char(1)     NOT NULL DEFAULT 'N',
    pw_expire_days       integer     NOT NULL DEFAULT 0,     -- 0 = 만료 없음
    login_max_fail       integer     NOT NULL DEFAULT 5,
    lock_minutes         integer     NOT NULL DEFAULT 15,
    session_timeout_min  integer     NOT NULL DEFAULT 120,
    updated_at           timestamptz NOT NULL DEFAULT now(),
    updated_by           varchar(40),
    PRIMARY KEY (tenant_id)
);
INSERT INTO sys_auth_policy (tenant_id) VALUES ('KINTEX')
ON CONFLICT (tenant_id) DO NOTHING;

-- ============================================================================
-- 5) 로그인 이력(신설) — 성공/실패. 이메일/IP 마스킹만 저장(원문 금지).
-- ============================================================================
CREATE TABLE IF NOT EXISTS login_history (
    tenant_id     varchar(40)  NOT NULL DEFAULT 'KINTEX',
    id            bigserial    NOT NULL,
    user_id       varchar(40),
    email_masked  varchar(200),                       -- kim***@do.com
    result        varchar(20)  NOT NULL DEFAULT 'SUCCESS',  -- SUCCESS|FAIL|LOCKED
    fail_reason   varchar(60),                         -- BAD_CREDENTIAL|LOCKED (요약)
    ip_masked     varchar(60),                         -- 203.0.113.* (원문 금지)
    user_agent    varchar(300),
    created_at    timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
);
CREATE INDEX IF NOT EXISTS idx_login_history_created ON login_history (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_login_history_user    ON login_history (tenant_id, user_id);
CREATE INDEX IF NOT EXISTS idx_login_history_result  ON login_history (tenant_id, result, created_at DESC);

-- ============================================================================
-- 6) 오류 로그(신설) — GlobalExceptionHandler 500 요약 1줄(스택트레이스 미저장).
-- ============================================================================
CREATE TABLE IF NOT EXISTS error_log (
    tenant_id   varchar(40)  NOT NULL DEFAULT 'KINTEX',
    id          bigserial    NOT NULL,
    error_code  varchar(60),                          -- INTERNAL 등
    summary     varchar(500),                         -- 예외 클래스 + 요약(민감정보·스택 제외)
    path        varchar(300),
    method      varchar(10),
    actor_id    varchar(40),
    created_at  timestamptz  NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
);
CREATE INDEX IF NOT EXISTS idx_error_log_created ON error_log (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_error_log_code    ON error_log (tenant_id, error_code, created_at DESC);

-- ============================================================================
-- 7) 공통코드 순증 — PARTNER_TYPE(회사 분류)
-- ============================================================================
INSERT INTO common_code_group (grp_code, grp_name, description, sort_order) VALUES
  ('PARTNER_TYPE', '거래처 분류', 'company 이식', 20)
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
  ('PARTNER_TYPE','CONTRACTOR','장치업체',1),
  ('PARTNER_TYPE','SUPPLIER','공급업체',2),
  ('PARTNER_TYPE','AGENCY','대행사',3),
  ('PARTNER_TYPE','ETC','기타',4)
ON CONFLICT (grp_code, code) DO UPDATE SET code_name = EXCLUDED.code_name, sort_order = EXCLUDED.sort_order;

-- ============================================================================
-- 8) 시스템관리 메뉴 순증 — 신규 화면(조직·프로그램·역할메뉴·인증정책·로그인이력·오류로그·시스템상태·메일/알림)
-- ============================================================================
INSERT INTO sys_menu (id, parent_id, menu_name, menu_path, icon, sort_order, required_role) VALUES
  ('m-sys-dept',   'm-sys', '조직 관리',    '/admin/depts',        'sitemap',  10, 'ADMIN'),
  ('m-sys-comp',   'm-sys', '회사 관리',    '/admin/companies',    'building', 11, 'ADMIN'),
  ('m-sys-prog',   'm-sys', '프로그램 관리','/admin/programs',     'window',   12, 'ADMIN'),
  ('m-sys-rmenu',  'm-sys', '역할-메뉴',    '/admin/role-menus',   'grid',     13, 'ADMIN'),
  ('m-sys-authp',  'm-sys', '인증 정책',    '/admin/auth-policy',  'lock',     14, 'ADMIN'),
  ('m-sys-login',  'm-sys', '로그인 이력',  '/admin/login-history','log-in',   15, 'ADMIN'),
  ('m-sys-errlog', 'm-sys', '오류 로그',    '/admin/error-log',    'alert',    16, 'ADMIN'),
  ('m-sys-health', 'm-sys', '시스템 상태',  '/admin/system-health','activity', 17, 'ADMIN'),
  ('m-sys-mail',   'm-sys', '메일 설정',    '/admin/mail-config',  'mail',     18, 'ADMIN'),
  ('m-sys-notify', 'm-sys', '알림 설정',    '/admin/notify-config','bell',     19, 'ADMIN')
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;

-- ============================================================================
-- 9) 메일/알림 설정 기본값(sys_setting) — mail.* / notify.* (시크릿은 secret_yn='Y')
-- ============================================================================
INSERT INTO sys_setting (setting_key, setting_value, value_type, description, secret_yn) VALUES
  ('mail.enabled',      'true',                 'BOOL',   '메일 발송 사용', 'N'),
  ('mail.host',         'mail.zioinfo.co.kr',   'STRING', 'SMTP 호스트', 'N'),
  ('mail.port',         '587',                  'INT',    'SMTP 포트', 'N'),
  ('mail.fromAddress',  'noreply@kintex.co.kr', 'STRING', '발신 주소', 'N'),
  ('mail.fromName',     'KINTEX',               'STRING', '발신자 표기', 'N'),
  ('mail.password',     '',                     'STRING', 'SMTP 비밀번호(env 주입 권장)', 'Y'),
  ('notify.email.enabled',   'true',  'BOOL', '이메일 알림 채널', 'N'),
  ('notify.inapp.enabled',   'true',  'BOOL', '인앱 알림 채널', 'N'),
  ('notify.webhook.enabled', 'false', 'BOOL', '웹훅 알림 채널', 'N')
ON CONFLICT (setting_key) DO NOTHING;
