-- =====================================================================
-- V14 M6 서류·마일스톤 (SCR-22/23) — event_milestone · required_document · document_review_issue
-- 멱등: CREATE TABLE IF NOT EXISTS + 시드 ON CONFLICT DO NOTHING.
-- 참조 컨벤션: event(id)·hall 등 snake_case, FK ON DELETE CASCADE.
-- 시드는 기존 실행사 e-2026-smf(2026 스마트팩토리 코리아, 개장 2026-08-11)를 참조.
-- V1~V13 불변 · 이 파일은 V14 단일 버전만 사용.
-- =====================================================================

-- 전시 마일스톤(행사별 표준 일정 노드) -----------------------------------
CREATE TABLE IF NOT EXISTS event_milestone (
    id             varchar(40)  PRIMARY KEY,
    event_id       varchar(40)  NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    milestone_type varchar(30)  NOT NULL,           -- assignment|pre_review|utility|documents|opening
    label          varchar(80)  NOT NULL,
    due_date       date,
    state          varchar(20)  NOT NULL DEFAULT 'todo',  -- done|active|todo
    sort_order     integer      NOT NULL DEFAULT 0,
    created_at     timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (event_id, milestone_type)
);
CREATE INDEX IF NOT EXISTS idx_event_milestone_event ON event_milestone(event_id);

-- 신고서류 체크리스트(행사별 필수 서류) ----------------------------------
CREATE TABLE IF NOT EXISTS required_document (
    id          varchar(40)  PRIMARY KEY,
    event_id    varchar(40)  NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    doc_type    varchar(60)  NOT NULL,              -- operation_plan|booth_layout|disaster_plan|...
    name        varchar(120) NOT NULL,
    status      varchar(20)  NOT NULL DEFAULT 'pending',  -- pending|draft|submitted|approved|rejected
    due_date    date,
    sort_order  integer      NOT NULL DEFAULT 0,
    updated_at  timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (event_id, doc_type)
);
CREATE INDEX IF NOT EXISTS idx_required_document_event ON required_document(event_id);

-- AI 서류 검수 이슈(행사별 불일치/누락 경고) -----------------------------
CREATE TABLE IF NOT EXISTS document_review_issue (
    id          varchar(40)  PRIMARY KEY,
    event_id    varchar(40)  NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    tone        varchar(10)  NOT NULL DEFAULT 'info',  -- warn|info
    title       varchar(120) NOT NULL,
    description varchar(400),
    sort_order  integer      NOT NULL DEFAULT 0,
    created_at  timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_document_review_issue_event ON document_review_issue(event_id);

-- ── 시드: e-2026-smf (Stitch SCR-22 정합) ──────────────────────────────
INSERT INTO event_milestone (id, event_id, milestone_type, label, due_date, state, sort_order) VALUES
  ('ms-smf-assign',   'e-2026-smf', 'assignment', 'D-150 배정',    DATE '2026-03-14', 'done',   0),
  ('ms-smf-prereview','e-2026-smf', 'pre_review', 'D-30 사전협의', DATE '2026-07-12', 'done',   1),
  ('ms-smf-utility',  'e-2026-smf', 'utility',    'D-25 유틸리티', DATE '2026-07-17', 'done',   2),
  ('ms-smf-docs',     'e-2026-smf', 'documents',  'D-7 신고서류',  DATE '2026-08-04', 'active', 3),
  ('ms-smf-opening',  'e-2026-smf', 'opening',    'D-0 개장',      DATE '2026-08-11', 'todo',   4)
ON CONFLICT (event_id, milestone_type) DO NOTHING;

INSERT INTO required_document (id, event_id, doc_type, name, status, due_date, sort_order) VALUES
  ('doc-smf-op',      'e-2026-smf', 'operation_plan', '행사운영계획서',       'approved',  NULL,              0),
  ('doc-smf-layout',  'e-2026-smf', 'booth_layout',   '부스배치도',           'submitted', NULL,              1),
  ('doc-smf-disaster','e-2026-smf', 'disaster_plan',  '재해대처계획서',       'draft',     DATE '2026-08-04', 2),
  ('doc-smf-rigging', 'e-2026-smf', 'rigging_calc',   '리깅 구조계산서',       'rejected',  DATE '2026-08-04', 3),
  ('doc-smf-fire',    'e-2026-smf', 'fire_safety',    '방화관리 책임서약서',   'pending',   DATE '2026-08-04', 4),
  ('doc-smf-parking', 'e-2026-smf', 'parking',        '주차관리 신청서',       'pending',   DATE '2026-08-04', 5),
  ('doc-smf-security','e-2026-smf', 'security',       '보안요원 배치계획',     'pending',   DATE '2026-08-04', 6),
  ('doc-smf-hazmat',  'e-2026-smf', 'hazmat',         '위험물 반입신고서',     'pending',   DATE '2026-08-04', 7)
ON CONFLICT (event_id, doc_type) DO NOTHING;

INSERT INTO document_review_issue (id, event_id, tone, title, description, sort_order) VALUES
  ('dri-smf-1', 'e-2026-smf', 'warn', '데이터 불일치 감지', '부스배치도 부스 수 486 ≠ 운영계획서 510 (불일치)', 0),
  ('dri-smf-2', 'e-2026-smf', 'info', '필수요소 누락',       '재해대처계획서 필수요소 누락 2건',                 1)
ON CONFLICT (id) DO NOTHING;
