-- ============================================================================
-- V49 — 잔여 빈 테이블 전수 보강 (화면이 쓰나 비어 있던 12개 도메인 테이블)
-- ============================================================================
-- 배경: V42 대량시드(40테이블)·V47(e-2026-live 폐루프) 이후에도, CREATE 는 되었으나
--   INSERT 가 전혀 없던(=화면에서 "빈 상태") 테이블이 다수 남아 있었다(매퍼 감사).
--   대상(전부 매퍼/서비스가 조회):
--     render_job · cms_media · logistics_equipment_request · logistics_rental_order ·
--     logistics_inbound · mail_log · edm_followup_log(+draft campaign) ·
--     webhook_subscription · webhook_delivery · login_history · sys_role_menu · audit_log
-- 스코프: 이벤트 종속 도메인은 두 데모 행사(e-2026-live=오늘 · e-2026-smf=8월) 모두 채워
--   행사 전환 시에도 물류/렌탈/입고/렌더/팔로업이 보이게 한다(고아 레코드 금지 — 기존 booth/lead/company/menu 참조).
-- 원칙: 멱등(ON CONFLICT DO NOTHING) · tenant_id='KINTEX' · PII 더미(홍길동·010-0000-*·*@kintex.test) ·
--   기존 시드 불변. 서버 최신 V47, 진행 중 V48(메시지관리)과 충돌 회피 → V49 확정.
--
-- 의도적으로 비워 두는 테이블(보안·운영성):
--   error_log(운영 500 요약만 기록) · password_reset(1회용 재설정 토큰) ·
--   webhook_inbound_endpoint / webhook_inbound_event(secret_enc AES-256-GCM 봉인은
--     앱 부팅 시더 전용 — SQL 평문 저장 금지, V35 주석 준수).
-- ============================================================================


-- ============================================================================
-- 1) render_job — 부스별 나노바나나 렌더 결과(상태 분포: DONE·RUNNING·QUEUED·FAILED)
-- ============================================================================
-- e-2026-live 부스(booth-live-01..06) + e-2026-smf 부스(booth-smf-a102/b101..b106).
INSERT INTO render_job (id, event_id, booth_id, shot_preset, status, image_url, schema_hash, model_version, error_message, created_at, updated_at) VALUES
  -- e-2026-live
  ('rj-live-01','e-2026-live','booth-live-01','S1','DONE','/uploads/render/rj-live-01.png','sha1-live01a','gemini-3.1-flash-image-preview',NULL, now()-interval '5 hour', now()-interval '5 hour'),
  ('rj-live-02','e-2026-live','booth-live-01','S3','DONE','/uploads/render/rj-live-02.png','sha1-live01b','gemini-3.1-flash-image-preview',NULL, now()-interval '5 hour', now()-interval '4 hour'),
  ('rj-live-03','e-2026-live','booth-live-02','S1','DONE','/uploads/render/rj-live-03.png','sha1-live02a','gemini-3.1-flash-image-preview',NULL, now()-interval '4 hour', now()-interval '3 hour'),
  ('rj-live-04','e-2026-live','booth-live-03','S2','RUNNING',NULL,'sha1-live03a','gemini-3.1-flash-image-preview',NULL, now()-interval '20 minute', now()-interval '2 minute'),
  ('rj-live-05','e-2026-live','booth-live-04','S1','QUEUED',NULL,'sha1-live04a',NULL,NULL, now()-interval '8 minute', now()-interval '8 minute'),
  ('rj-live-06','e-2026-live','booth-live-05','S4','FAILED',NULL,'sha1-live05a','gemini-3.1-flash-image-preview','생성 시간 초과 — 재시도 대기(요약).', now()-interval '2 hour', now()-interval '2 hour'),
  ('rj-live-07','e-2026-live','booth-live-06','S1','DONE','/uploads/render/rj-live-07.png','sha1-live06a','gemini-3.1-flash-image-preview',NULL, now()-interval '6 hour', now()-interval '6 hour'),
  -- e-2026-smf
  ('rj-smf-01','e-2026-smf','booth-smf-a102','S1','DONE','/uploads/render/rj-smf-01.png','sha1-smfa102','gemini-3.1-flash-image-preview',NULL, TIMESTAMPTZ '2026-07-20 10:00+09', TIMESTAMPTZ '2026-07-20 10:03+09'),
  ('rj-smf-02','e-2026-smf','booth-smf-b101','S2','DONE','/uploads/render/rj-smf-02.png','sha1-smfb101','gemini-3.1-flash-image-preview',NULL, TIMESTAMPTZ '2026-07-21 11:00+09', TIMESTAMPTZ '2026-07-21 11:04+09'),
  ('rj-smf-03','e-2026-smf','booth-smf-b102','S1','DONE','/uploads/render/rj-smf-03.png','sha1-smfb102','gemini-3.1-flash-image-preview',NULL, TIMESTAMPTZ '2026-07-22 09:30+09', TIMESTAMPTZ '2026-07-22 09:33+09'),
  ('rj-smf-04','e-2026-smf','booth-smf-b103','S3','QUEUED',NULL,'sha1-smfb103',NULL,NULL, TIMESTAMPTZ '2026-07-23 14:00+09', TIMESTAMPTZ '2026-07-23 14:00+09'),
  ('rj-smf-05','e-2026-smf','booth-smf-b104','S5','FAILED',NULL,'sha1-smfb104','gemini-3.1-flash-image-preview','입력 평면도 해상도 부족 — 재업로드 요망(요약).', TIMESTAMPTZ '2026-07-23 15:20+09', TIMESTAMPTZ '2026-07-23 15:21+09')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 2) cms_media — 미디어 라이브러리(전역 + 행사별)
-- ============================================================================
INSERT INTO cms_media (id, event_id, file_name, url, content_type, size_bytes, alt_text, uploader_name, created_at) VALUES
  ('med-global-01', NULL,          'kintex-logo.svg',        '/uploads/cms/kintex-logo.svg',      'image/svg+xml',  18240,  'KINTEX AI 전시·행사시스템 로고', '관리자', now()-interval '30 day'),
  ('med-global-02', NULL,          'hero-hall.jpg',          '/uploads/cms/hero-hall.jpg',        'image/jpeg',    482113,  '킨텍스 전시홀 메인 히어로', '관리자', now()-interval '25 day'),
  ('med-global-03', NULL,          'brochure-cover.png',     '/uploads/cms/brochure-cover.png',   'image/png',     231544,  '공식 도록 표지', '관리자', now()-interval '20 day'),
  ('med-live-01','e-2026-live',    'smart-auto-poster.jpg',  '/uploads/cms/smart-auto-poster.jpg','image/jpeg',    356820,  '스마트 물류·자동화 코리아 포스터', '홍길동', now()-interval '10 day'),
  ('med-live-02','e-2026-live',    'floorplan-live.png',     '/uploads/cms/floorplan-live.png',   'image/png',     512900,  'e-2026-live 홀 배치도', '홍길동', now()-interval '7 day'),
  ('med-live-03','e-2026-live',    'sponsor-banner.jpg',     '/uploads/cms/sponsor-banner.jpg',   'image/jpeg',    198340,  '스폰서 배너(다이아몬드)', '홍길동', now()-interval '3 day'),
  ('med-smf-01','e-2026-smf',      'smf-keyvisual.jpg',      '/uploads/cms/smf-keyvisual.jpg',    'image/jpeg',    421077,  '스마트 팩토리 엑스포 키비주얼', '관리자', TIMESTAMPTZ '2026-07-15 10:00+09'),
  ('med-smf-02','e-2026-smf',      'smf-map.png',            '/uploads/cms/smf-map.png',          'image/png',     488213,  'e-2026-smf 전시장 안내도', '관리자', TIMESTAMPTZ '2026-07-16 10:00+09'),
  ('med-smf-03','e-2026-smf',      'smf-speaker.jpg',        '/uploads/cms/smf-speaker.jpg',      'image/jpeg',    167902,  '기조연설 연사 프로필(더미)', '관리자', TIMESTAMPTZ '2026-07-17 10:00+09')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 3) logistics_equipment_request — 부대장비 신청(마스터 스냅샷, 상태 분포)
-- ============================================================================
INSERT INTO logistics_equipment_request
    (id, event_id, equipment_id, equipment_name, unit_price, booth_no, quantity, use_date, days, amount, status, note, reject_reason, requested_by, decided_by, decided_at) VALUES
  ('ler-live-01','e-2026-live','leq-fork-25','지게차 2.5t',180000,'L-101',1, CURRENT_DATE,     1, 180000,'approved','반입일 오전 상차','', 'demo-exhibitor','demo-organizer', now()-interval '1 day'),
  ('ler-live-02','e-2026-live','leq-manp-01','상하차 인력', 90000,'L-102',2, CURRENT_DATE,     1, 180000,'requested','대형 장비 상하차 인력 2인','', 'demo-exhibitor',NULL,NULL),
  ('ler-live-03','e-2026-live','leq-lift-01','고소작업 리프트',150000,'L-103',1, CURRENT_DATE+1, 1, 150000,'done','트러스 설치 완료','', 'demo-exhibitor','demo-organizer', now()-interval '2 day'),
  ('ler-smf-01','e-2026-smf','leq-fork-05','지게차 5t',260000,'A-102',1, DATE '2026-08-04', 1, 260000,'approved','반입일 대형 화물 상차','', 'demo-exhibitor','demo-organizer', TIMESTAMPTZ '2026-07-28 10:00+09'),
  ('ler-smf-02','e-2026-smf','leq-lift-01','고소작업 리프트',150000,'B-101',1, DATE '2026-08-04', 2, 300000,'requested','2일간 사이니지 설치','', 'demo-exhibitor',NULL,NULL),
  ('ler-smf-03','e-2026-smf','leq-crane-5','이동식 크레인 5t',420000,'B-104',1, DATE '2026-08-05', 1, 420000,'rejected','','중량물 크레인은 안전관리계획서 선제출 필요', 'demo-exhibitor','demo-organizer', TIMESTAMPTZ '2026-07-29 14:00+09')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 4) logistics_rental_order — 렌탈 품목 주문(마스터 스냅샷, 상태 분포)
-- ============================================================================
INSERT INTO logistics_rental_order
    (id, event_id, item_id, item_name, daily_price, booth_no, quantity, start_date, end_date, days, amount, status, note, ordered_by, decided_by, decided_at) VALUES
  ('lro-live-01','e-2026-live','lri-tv-55','55인치 모니터',60000,'L-101',2, CURRENT_DATE, CURRENT_DATE+2, 3, 360000,'confirmed','부스 시연용 2대','demo-exhibitor','demo-organizer', now()-interval '1 day'),
  ('lro-live-02','e-2026-live','lri-chair-01','상담용 의자',3000,'L-101',10, CURRENT_DATE, CURRENT_DATE+2, 3, 90000,'ordered','상담 부스 의자','demo-exhibitor',NULL,NULL),
  ('lro-live-03','e-2026-live','lri-info-01','인포메이션 데스크',25000,'L-102',1, CURRENT_DATE, CURRENT_DATE+2, 3, 75000,'delivered','안내 데스크 설치 완료','demo-exhibitor','demo-organizer', now()-interval '6 hour'),
  ('lro-smf-01','e-2026-smf','lri-table-01','접이식 테이블',8000,'A-102',5, DATE '2026-08-04', DATE '2026-08-07', 4, 160000,'confirmed','상담존 테이블','demo-exhibitor','demo-organizer', TIMESTAMPTZ '2026-07-28 11:00+09'),
  ('lro-smf-02','e-2026-smf','lri-spot-01','LED 스포트 조명',5000,'B-101',12, DATE '2026-08-04', DATE '2026-08-07', 4, 240000,'ordered','제품 진열 조명','demo-exhibitor',NULL,NULL),
  ('lro-smf-03','e-2026-smf','lri-shelf-01','진열 선반',12000,'B-103',3, DATE '2026-08-04', DATE '2026-08-07', 4, 144000,'cancelled','발주 취소(부스 설계 변경)','demo-exhibitor','demo-organizer', TIMESTAMPTZ '2026-07-30 09:00+09')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 5) logistics_inbound — 반입 화물 추적(운송장, 상태 분포)
-- ============================================================================
INSERT INTO logistics_inbound
    (id, event_id, tracking_no, carrier, booth_no, item_desc, quantity, status, eta_date, warehouse_at, delivered_at, note, registered_by, updated_by) VALUES
  ('lib-live-01','e-2026-live','CJ-1000-2201','CJ대한통운','L-101','AMR 시연 장비 3종',3,'delivered_booth', CURRENT_DATE, now()-interval '8 hour', now()-interval '3 hour','부스 전달 완료','demo-exhibitor','demo-organizer'),
  ('lib-live-02','e-2026-live','LO-2200-3310','로젠택배','L-102','비전 검사 모듈 팔레트',1,'arrived_warehouse', CURRENT_DATE, now()-interval '2 hour', NULL,'창고 대기 — 부스 전달 예정','demo-exhibitor','demo-organizer'),
  ('lib-live-03','e-2026-live','HJ-3300-4420','한진택배','L-103','협동로봇 본체',2,'inbound_scheduled', CURRENT_DATE+1, NULL, NULL,'익일 입고 예정','demo-exhibitor',NULL),
  ('lib-smf-01','e-2026-smf','CJ-5000-1102','CJ대한통운','A-102','스마트팩토리 데모 셀',1,'registered', DATE '2026-08-03', NULL, NULL,'반입 신청 등록','demo-exhibitor',NULL),
  ('lib-smf-02','e-2026-smf','LO-5100-2203','로젠택배','B-101','전자 사이니지 패널 6매',6,'inbound_scheduled', DATE '2026-08-03', NULL, NULL,'개막 전일 입고','demo-exhibitor','demo-organizer')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 6) mail_log — 발송 이력(제목/마스킹 수신자만, 본문·원문 미저장)
-- ============================================================================
INSERT INTO mail_log (tenant_id, id, category, ref_id, subject, recipient_masked, status, detail, created_at) VALUES
  ('KINTEX','ml-0001','AUCTION_OPEN','AUC-LIVE-01','[킨텍스] L-101 아일랜드 부스 장치공사 옥션 초대','han***@kintex.test', 'sent','초대 공사업체 3사 발송', now()-interval '3 day'),
  ('KINTEX','ml-0002','AUCTION_OPEN','AUC-LIVE-02','[킨텍스] 메인 통로 전기·조명 설치 옥션 초대','del***@kintex.test', 'sent','초대 공사업체 3사 발송', now()-interval '2 day'),
  ('KINTEX','ml-0003','EDM','cp-live-001','[스마트 물류·자동화 코리아] 개막 안내','vis***@kintex.test', 'sent','사전등록자 5,400명 대상 발송', now()-interval '1 day'),
  ('KINTEX','ml-0004','EDM','cp-live-002','[VIP 초대] 바이어 상담회 안내','buy***@kintex.test', 'sent','VIP 1,200명 대상 발송 예약', now()-interval '6 hour'),
  ('KINTEX','ml-0005','PASSWORD_RESET',NULL,'[킨텍스] 비밀번호 재설정 안내','dem***@kintex.test','sent','재설정 링크 발송(유효 30분)', now()-interval '5 hour'),
  ('KINTEX','ml-0006','EDM','cp-smf-001','[스마트 팩토리 엑스포] 사전등록 오픈 안내','pre***@kintex.test','sent','사전등록 안내 발송', TIMESTAMPTZ '2026-07-18 10:00+09'),
  ('KINTEX','ml-0007','AUCTION_OPEN','AUC-2026-0511','[킨텍스] D-1 옥외 사이니지 설치 옥션 초대','sec***@kintex.test','failed','수신 도메인 일시 오류 — 재발송 대상', TIMESTAMPTZ '2026-07-25 14:00+09'),
  ('KINTEX','ml-0008','EDM','cp-smf-003','[스마트 팩토리 엑스포] 세미나 프로그램 안내','edu***@kintex.test','skipped','수신거부 목록 필터링', TIMESTAMPTZ '2026-07-27 10:00+09')
ON CONFLICT (tenant_id, id) DO NOTHING;


-- ============================================================================
-- 7) edm_followup_log(+ draft campaign) — 핫리드(score>=80) 팔로업 자동 초안 1회
-- ============================================================================
-- 리드당 초안 캠페인(draft) 1건 생성 후 followup_log 로 링크(멱등 키: UNIQUE(event_id, lead_id)).
INSERT INTO edm_campaign (id, event_id, name, status, audience, meta, open_rate, click_rate) VALUES
  ('cp-fu-live-001','e-2026-live','[팔로업] 스마트 물류 AMR 커스텀 제안','draft', 1, 'AI 자동 초안 · ld-live-001', '-','-'),
  ('cp-fu-live-002','e-2026-live','[팔로업] AI 비전 검사 모듈 파일럿 안내','draft', 1, 'AI 자동 초안 · ld-live-002', '-','-'),
  ('cp-fu-smf-001','e-2026-smf','[팔로업] 협동로봇 HR-A1 도입 제안','draft', 1, 'AI 자동 초안 · ld-smf-006', '-','-'),
  ('cp-fu-smf-002','e-2026-smf','[팔로업] 협동로봇 HR-A1 견적 안내','draft', 1, 'AI 자동 초안 · ld-smf-001', '-','-'),
  ('cp-fu-smf-003','e-2026-smf','[팔로업] AI 비전 검사 모듈 사례집','draft', 1, 'AI 자동 초안 · ld-smf-003', '-','-')
ON CONFLICT (id) DO NOTHING;

INSERT INTO edm_followup_log (tenant_id, id, event_id, lead_id, campaign_id, hot_score, body_draft, created_at) VALUES
  ('KINTEX','fu-live-001','e-2026-live','ld-live-001','cp-fu-live-001',92,
     '안녕하세요, 홍 팀장님. 스마트 물류 AMR 커스텀 제안서를 준비했습니다. 다음 주 미팅 가능하실까요?', now()-interval '3 hour'),
  ('KINTEX','fu-live-002','e-2026-live','ld-live-002','cp-fu-live-002',85,
     '안녕하세요, 김 님. 요청하신 AI 비전 검사 모듈 견적서와 파일럿 프로그램 안내드립니다.', now()-interval '5 hour'),
  ('KINTEX','fu-smf-006','e-2026-smf','ld-smf-006','cp-fu-smf-001',91,
     '안녕하세요, 문 팀장님. 협동로봇 HR-A1 도입 제안서를 보내드립니다.', TIMESTAMPTZ '2026-08-01 15:00+09'),
  ('KINTEX','fu-smf-001','e-2026-smf','ld-smf-001','cp-fu-smf-002',87,
     '안녕하세요, 김 담당자님. 협동로봇 HR-A1 견적서를 준비했습니다.', TIMESTAMPTZ '2026-08-01 15:10+09'),
  ('KINTEX','fu-smf-003','e-2026-smf','ld-smf-003','cp-fu-smf-003',82,
     '안녕하세요, 박 소장님. AI 비전 검사 모듈 도입 사례집을 보내드립니다.', TIMESTAMPTZ '2026-08-01 15:20+09')
-- 멱등 키는 업무 유니크 (event_id, lead_id) — V47/V42 가 동일 리드에 이미 초안을 시드했으면(다른 id) 건너뛴다.
--   (id PK 는 신규이므로 실충돌은 항상 (event_id, lead_id) 쪽에서 발생.)
ON CONFLICT (event_id, lead_id) DO NOTHING;


-- ============================================================================
-- 8) webhook_subscription — 아웃바운드 구독(발송 disabled 스텁)
-- ============================================================================
-- secret_hash = SHA-256(secret) hex 자리(원문 미저장·발송 disabled 이므로 검증 불요). 데모 고정 해시.
INSERT INTO webhook_subscription (tenant_id, id, event_type, callback_url, secret_hash, description, active, created_by) VALUES
  ('KINTEX','whs-001','lead.hot',       'https://hooks.example.test/kintex/lead-hot',   '3a7bd3e2360a3d29eea436fcfb7e44c735d117c42d1c1835420b6b9942dd4f1b','핫리드 발생 시 CRM 연동(스텁)', true, 'system-seed'),
  ('KINTEX','whs-002','invoice.paid',   'https://hooks.example.test/kintex/invoice-paid','5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8','청구 완납 시 회계 연동(스텁)', true, 'system-seed'),
  ('KINTEX','whs-003','auction.awarded','https://hooks.example.test/kintex/award',       '6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b','옥션 낙찰 시 계약 연동(스텁)', true, 'system-seed'),
  ('KINTEX','whs-004','visitor.checkin','https://hooks.example.test/kintex/checkin',     'd4735e3a265e16eee03f59718b9b5d03019c07d8b6c51f90da3a666eec13ab35','현장 체크인 실시간 연동(스텁)', false, 'system-seed')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 9) webhook_delivery — 아웃바운드 발송 큐(status=disabled 스텁, 실 HTTP 콜 없음)
-- ============================================================================
INSERT INTO webhook_delivery (tenant_id, id, subscription_id, event_type, payload_json, status, note, created_at) VALUES
  ('KINTEX','whd-001','whs-001','lead.hot',
     '{"eventId":"e-2026-live","leadId":"ld-live-001","score":92}'::text,'disabled','스텁 — 실발송 없음', now()-interval '3 hour'),
  ('KINTEX','whd-002','whs-002','invoice.paid',
     '{"eventId":"e-2026-live","invoiceId":"inv-live-01","total":7187400}'::text,'disabled','스텁 — 실발송 없음', now()-interval '5 day'),
  ('KINTEX','whd-003','whs-003','auction.awarded',
     '{"eventId":"e-2026-live","auctionId":"AUC-LIVE-01","awardId":"aw-live-01"}'::text,'disabled','스텁 — 실발송 없음', now()-interval '1 day'),
  ('KINTEX','whd-004','whs-001','lead.hot',
     '{"eventId":"e-2026-smf","leadId":"ld-smf-006","score":91}'::text,'disabled','스텁 — 실발송 없음', TIMESTAMPTZ '2026-08-01 15:00+09')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 10) login_history — 로그인 이력(마스킹 이메일·IP, 결과 분포)
-- ============================================================================
-- id 는 bigserial → 명시하지 않고 자동 채번. 재실행 시 중복 방지 위해 NOT EXISTS 가드.
INSERT INTO login_history (tenant_id, user_id, email_masked, result, fail_reason, ip_masked, user_agent, created_at)
SELECT * FROM (VALUES
  ('KINTEX','demo-organizer', 'org***@kintex.test','SUCCESS',NULL,          '203.0.113.*', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/126', now()-interval '2 hour'),
  ('KINTEX','demo-exhibitor', 'exh***@kintex.test','SUCCESS',NULL,          '203.0.113.*', 'Mozilla/5.0 (Macintosh; Intel Mac OS X) Safari/17',   now()-interval '5 hour'),
  ('KINTEX','demo-contractor','con***@kintex.test','FAIL',   'BAD_CREDENTIAL','198.51.100.*','Mozilla/5.0 (Windows NT 10.0; Win64; x64) Edge/126',  now()-interval '1 day'),
  ('KINTEX','demo-contractor','con***@kintex.test','SUCCESS',NULL,          '198.51.100.*','Mozilla/5.0 (Windows NT 10.0; Win64; x64) Edge/126',  now()-interval '23 hour'),
  ('KINTEX','demo-visitor',   'vis***@kintex.test','SUCCESS',NULL,          '203.0.113.*', 'Mozilla/5.0 (Linux; Android 14) Chrome/126 Mobile',  now()-interval '40 minute'),
  ('KINTEX', NULL,            'unk***@kintex.test','FAIL',   'BAD_CREDENTIAL','198.51.100.*','Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/125',now()-interval '3 day'),
  ('KINTEX','demo-exhibitor', 'exh***@kintex.test','LOCKED', 'LOCKED',       '198.51.100.*','Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/126',now()-interval '2 day')
) AS v(tenant_id, user_id, email_masked, result, fail_reason, ip_masked, user_agent, created_at)
WHERE NOT EXISTS (SELECT 1 FROM login_history WHERE user_agent LIKE 'Mozilla/5.0%' AND ip_masked IN ('203.0.113.*','198.51.100.*') LIMIT 1);


-- ============================================================================
-- 11) sys_role_menu — 역할-메뉴 권한 매핑(RBAC 조회 화면 /admin/role-menus)
-- ============================================================================
-- ADMIN: 전 메뉴 읽기/쓰기. MANAGER: 업무(m-work*) 읽기/쓰기. USER: 업무 읽기전용.
INSERT INTO sys_role_menu (role_code, menu_id, read_yn, write_yn)
SELECT 'ADMIN', id, 'Y', 'Y' FROM sys_menu
ON CONFLICT (role_code, menu_id) DO NOTHING;

INSERT INTO sys_role_menu (role_code, menu_id, read_yn, write_yn)
SELECT 'MANAGER', id, 'Y', 'Y' FROM sys_menu WHERE id LIKE 'm-work%'
ON CONFLICT (role_code, menu_id) DO NOTHING;

INSERT INTO sys_role_menu (role_code, menu_id, read_yn, write_yn)
SELECT 'USER', id, 'Y', 'N' FROM sys_menu WHERE id LIKE 'm-work%'
ON CONFLICT (role_code, menu_id) DO NOTHING;


-- ============================================================================
-- 12) audit_log — 감사 로그(관리 조회 화면 /admin/audit)
-- ============================================================================
-- id 는 bigserial 자동 채번. 재실행 중복 방지 위해 대표 summary 존재 여부로 가드.
INSERT INTO audit_log (actor_id, actor_name, action, target_type, target_id, event_id, summary, ruleset_version, result, ip_hint, created_at)
SELECT * FROM (VALUES
  ('demo-organizer','주최자(데모)','AWARD_SELECT','auction','AUC-LIVE-01','e-2026-live','옥션 낙찰 선정 — (주)한빛부스디자인(4,200,000원)', 'auction-v1.0','SUCCESS','203.0.113.*', now()-interval '1 day'),
  ('demo-organizer','주최자(데모)','INVOICE_ISSUE','invoice','inv-live-01','e-2026-live','청구서 발행 — (주)스마트오토메이션 7,187,400원', 'billing-v1.0','SUCCESS','203.0.113.*', now()-interval '18 day'),
  ('demo-organizer','주최자(데모)','PAYMENT_RECORD','invoice','inv-live-01','e-2026-live','수납 등록 — 잔금 4,312,440원', 'billing-v1.0','SUCCESS','203.0.113.*', now()-interval '5 day'),
  ('demo-organizer','주최자(데모)','REFUND_RECORD','invoice','inv-live-08','e-2026-live','환불 산정 — 1,320,000원(개막일 이후 50%)', 'refund-v1.0','SUCCESS','203.0.113.*', now()-interval '2 day'),
  ('demo-exhibitor','참가업체(데모)','LEAD_EXPORT','lead',NULL,'e-2026-live','리드 6건 내보내기(CSV)', NULL,'SUCCESS','203.0.113.*', now()-interval '4 hour'),
  ('demo-contractor','공사업체(데모)','BID_SUBMIT','auction','AUC-LIVE-02','e-2026-live','응찰 제출 — 3,650,000원', 'auction-v1.0','SUCCESS','198.51.100.*', now()-interval '12 hour'),
  ('admin','시스템관리자','ROLE_MENU_UPDATE','sys_role_menu','MANAGER','','역할-메뉴 권한 변경 — MANAGER 업무 메뉴 쓰기 허용', NULL,'SUCCESS','203.0.113.*', now()-interval '10 day'),
  ('demo-organizer','주최자(데모)','AWARD_SELECT','auction','AUC-2026-0210','e-2026-smf','옥션 낙찰 선정 — 세계전기시설(12,000,000원)', 'auction-v1.0','SUCCESS','203.0.113.*', TIMESTAMPTZ '2026-07-30 11:00+09')
) AS v(actor_id, actor_name, action, target_type, target_id, event_id, summary, ruleset_version, result, ip_hint, created_at)
WHERE NOT EXISTS (SELECT 1 FROM audit_log WHERE action='AWARD_SELECT' AND target_id='AUC-LIVE-01');
