-- ============================================================================
-- V68 — 데모 참가업체 대량 보강 (e-2026-live) + 월별 부스/매출 추이·손익 소스
-- 담당: kintex-testdata-dev · 작성: 2026-07-23
-- ----------------------------------------------------------------------------
-- 목적(소유자 시연 지시 항목 1·6):
--   ① 참가업체 현황(대시보드 위젯 + 참가업체관리 SCR): e-2026-live 참가사 6 → 24로 확대,
--      상태 분포(부스배정/계약/승인/입금/신청) + 업종 다양화.
--   ② 경영분석(M16) 월별 부스·매출 추이: AnalyticsMapper.findMonthlyTrend 는 booth.created_at 을
--      'YYYY-MM' 로 그룹 → 신규 부스 created_at 을 최근 12개월에 분산해 추이 차트를 채운다.
--   ③ 손익(P&L): 매출 = 판매면적(ST_Area)×요율(master_data RATE 2250) → 부스 증가로 임대 매출 상승.
-- ----------------------------------------------------------------------------
-- 근거(실 조회 경로 역추적):
--   • ExhibitorMapper.findExhibitors: booth(assigned_company_name!=null) ← layout.event_id
--       + booth_sale(saleStatus) + design_plan 최신(designStatus) + invoice(invoiceStatus·outstanding)
--       + event_member/company(연락처). → 참가사 = 배정 부스.
--   • DashboardMapper.findExhibitors: booth + design_plan (대시보드 참가업체 현황 위젯).
--   • AnalyticsMapper.findMonthlyTrend/findAreaAndCount: booth ST_Area·created_at (per-event 추이·KPI·P&L).
-- 원칙: e-2026-live(=workspaces[0], start_date 최소) 스코프. tenant_id='KINTEX'. PII 더미.
--   전부 순수 INSERT ... ON CONFLICT DO NOTHING (멱등·재실행 안전). 기존 booth-live-* 불변.
--   부스는 기존 layout 'ly-live'(H1)에 순증. booth_no = L-109..L-126(기존 L-101..L-108 비충돌).
--   geom: 기존 부스(y 0..7) 아래 y=8+ 그리드에 6m×3m 타일 배치(면적 집계용 · 충돌 무해).
-- ============================================================================


-- 1) 참가업체(전시 참가사) 마스터 순증 — booth.assigned_company_name 표시명과 정합. 연락처 조인용.
INSERT INTO company (id, registration_no, name, category, region, registered, phone) VALUES
  ('co-ex-11','311-81-10011','(주)오토닉스로보',   '로봇/자동화',   '경기', true, '031-000-2011'),
  ('co-ex-12','312-81-10012','한성정밀기계',       '기계/장비',     '경남', true, '055-000-2012'),
  ('co-ex-13','313-81-10013','대성전자',           '전기/전자',     '인천', true, '032-000-2013'),
  ('co-ex-14','314-81-10014','스마트팩토리솔루션', 'ICT/소프트웨어','서울', true, '02-0000-2014'),
  ('co-ex-15','315-81-10015','코리아센서',         '센서/계측',     '대전', true, '042-000-2015'),
  ('co-ex-16','316-81-10016','퓨처모빌리티',       '모빌리티',      '경기', true, '031-000-2016'),
  ('co-ex-17','317-81-10017','그린에너지테크',     '에너지/환경',   '충남', true, '041-000-2017'),
  ('co-ex-18','318-81-10018','나노소재랩',         '소재/화학',     '울산', true, '052-000-2018'),
  ('co-ex-19','319-81-10019','비전AI시스템',       'AI/비전',       '서울', true, '02-0000-2019'),
  ('co-ex-20','320-81-10020','한국물류로보틱스',   '물류/유통',     '부산', true, '051-000-2020'),
  ('co-ex-21','321-81-10021','메디컬디바이스코',   '바이오/헬스',   '서울', true, '02-0000-2021'),
  ('co-ex-22','322-81-10022','클린테크환경',       '에너지/환경',   '경기', true, '031-000-2022'),
  ('co-ex-23','323-81-10023','디지털트윈랩',       'ICT/소프트웨어','대구', true, '053-000-2023'),
  ('co-ex-24','324-81-10024','정밀계측기술',       '센서/계측',     '경기', true, '031-000-2024'),
  ('co-ex-25','325-81-10025','엣지컴퓨팅코리아',   'ICT/소프트웨어','서울', true, '02-0000-2025'),
  ('co-ex-26','326-81-10026','스마트팜테크',       '농업/식품',     '전북', true, '063-000-2026'),
  ('co-ex-27','327-81-10027','3D프린팅솔루션',     '제조/자동화',   '경기', true, '031-000-2027'),
  ('co-ex-28','328-81-10028','차세대배터리',       '에너지/환경',   '충북', true, '043-000-2028')
ON CONFLICT (registration_no) DO NOTHING;


-- 2) 부스 18(POLYGON) — layout ly-live 순증. created_at 을 최근 12개월에 분산(월별 추이 소스).
--    booth_type·premium 다양화. assigned_company_name = 위 참가사(참가업체 목록/대시보드 위젯 채움).
INSERT INTO booth (id, layout_id, booth_no, booth_type, geom, size_w_m, size_d_m, height_m,
                   floor_load_t_per_m2, assigned_company_name, premium, created_at) VALUES
  ('booth-live-09','ly-live','L-109','island',      ST_GeomFromText('POLYGON((0 8,6 8,6 11,0 11,0 8))',0),      6,3,4.0,1.0,'(주)오토닉스로보',   true,  now()-interval '330 day'),
  ('booth-live-10','ly-live','L-110','assembled',   ST_GeomFromText('POLYGON((7 8,13 8,13 11,7 11,7 8))',0),    6,3,3.0,1.0,'한성정밀기계',       false, now()-interval '300 day'),
  ('booth-live-11','ly-live','L-111','independent', ST_GeomFromText('POLYGON((14 8,20 8,20 11,14 11,14 8))',0), 6,3,3.5,1.0,'대성전자',           false, now()-interval '270 day'),
  ('booth-live-12','ly-live','L-112','corner',      ST_GeomFromText('POLYGON((21 8,27 8,27 11,21 11,21 8))',0), 6,3,3.5,1.0,'스마트팩토리솔루션', false, now()-interval '240 day'),
  ('booth-live-13','ly-live','L-113','assembled',   ST_GeomFromText('POLYGON((0 12,6 12,6 15,0 15,0 12))',0),    6,3,3.0,1.0,'코리아센서',        false, now()-interval '210 day'),
  ('booth-live-14','ly-live','L-114','island',      ST_GeomFromText('POLYGON((7 12,13 12,13 15,7 15,7 12))',0),  6,3,4.0,1.0,'퓨처모빌리티',      true,  now()-interval '180 day'),
  ('booth-live-15','ly-live','L-115','assembled',   ST_GeomFromText('POLYGON((14 12,20 12,20 15,14 15,14 12))',0),6,3,3.0,1.0,'그린에너지테크',    false, now()-interval '165 day'),
  ('booth-live-16','ly-live','L-116','independent', ST_GeomFromText('POLYGON((21 12,27 12,27 15,21 15,21 12))',0),6,3,3.5,1.0,'나노소재랩',        false, now()-interval '150 day'),
  ('booth-live-17','ly-live','L-117','island',      ST_GeomFromText('POLYGON((0 16,6 16,6 19,0 19,0 16))',0),    6,3,4.0,1.0,'비전AI시스템',      true,  now()-interval '120 day'),
  ('booth-live-18','ly-live','L-118','corner',      ST_GeomFromText('POLYGON((7 16,13 16,13 19,7 19,7 16))',0),  6,3,3.5,1.0,'한국물류로보틱스',  false, now()-interval '100 day'),
  ('booth-live-19','ly-live','L-119','assembled',   ST_GeomFromText('POLYGON((14 16,20 16,20 19,14 19,14 16))',0),6,3,3.0,1.0,'메디컬디바이스코',  false, now()-interval '90 day'),
  ('booth-live-20','ly-live','L-120','assembled',   ST_GeomFromText('POLYGON((21 16,27 16,27 19,21 19,21 16))',0),6,3,3.0,1.0,'클린테크환경',      false, now()-interval '75 day'),
  ('booth-live-21','ly-live','L-121','independent', ST_GeomFromText('POLYGON((0 20,6 20,6 23,0 23,0 20))',0),    6,3,3.5,1.0,'디지털트윈랩',      false, now()-interval '60 day'),
  ('booth-live-22','ly-live','L-122','assembled',   ST_GeomFromText('POLYGON((7 20,13 20,13 23,7 23,7 20))',0),  6,3,3.0,1.0,'정밀계측기술',      false, now()-interval '45 day'),
  ('booth-live-23','ly-live','L-123','corner',      ST_GeomFromText('POLYGON((14 20,20 20,20 23,14 23,14 20))',0),6,3,3.5,1.0,'엣지컴퓨팅코리아',  false, now()-interval '30 day'),
  ('booth-live-24','ly-live','L-124','assembled',   ST_GeomFromText('POLYGON((21 20,27 20,27 23,21 23,21 20))',0),6,3,3.0,1.0,'스마트팜테크',      false, now()-interval '20 day'),
  ('booth-live-25','ly-live','L-125','island',      ST_GeomFromText('POLYGON((0 24,6 24,6 27,0 27,0 24))',0),    6,3,4.0,1.0,'3D프린팅솔루션',    true,  now()-interval '10 day'),
  ('booth-live-26','ly-live','L-126','independent', ST_GeomFromText('POLYGON((7 24,13 24,13 27,7 27,7 24))',0),  6,3,3.5,1.0,'차세대배터리',      false, now()-interval '4 day')
ON CONFLICT (id) DO NOTHING;


-- 3) 판매 오버레이(booth_sale) — 계약(sold) 다수 + held/available 소수. UNIQUE(event_id,booth_id) 멱등.
INSERT INTO booth_sale (tenant_id, id, event_id, hall_id, booth_id, booth_no, status, unit_price,
                        area_m2, held_by, held_until, sold_to_company, sold_by, sold_at,
                        blocked_reason, created_by) VALUES
  ('KINTEX','bs-live-09','e-2026-live','H1','booth-live-09','L-109','sold',      6534000,18,NULL,NULL,'(주)오토닉스로보',  'demo-organizer',now()-interval '320 day',NULL,'system-seed'),
  ('KINTEX','bs-live-10','e-2026-live','H1','booth-live-10','L-110','sold',      4356000,18,NULL,NULL,'한성정밀기계',      'demo-organizer',now()-interval '290 day',NULL,'system-seed'),
  ('KINTEX','bs-live-11','e-2026-live','H1','booth-live-11','L-111','sold',      4791600,18,NULL,NULL,'대성전자',          'demo-organizer',now()-interval '260 day',NULL,'system-seed'),
  ('KINTEX','bs-live-12','e-2026-live','H1','booth-live-12','L-112','sold',      4791600,18,NULL,NULL,'스마트팩토리솔루션','demo-organizer',now()-interval '230 day',NULL,'system-seed'),
  ('KINTEX','bs-live-13','e-2026-live','H1','booth-live-13','L-113','sold',      4356000,18,NULL,NULL,'코리아센서',        'demo-organizer',now()-interval '205 day',NULL,'system-seed'),
  ('KINTEX','bs-live-14','e-2026-live','H1','booth-live-14','L-114','sold',      6534000,18,NULL,NULL,'퓨처모빌리티',      'demo-organizer',now()-interval '175 day',NULL,'system-seed'),
  ('KINTEX','bs-live-15','e-2026-live','H1','booth-live-15','L-115','sold',      4356000,18,NULL,NULL,'그린에너지테크',    'demo-organizer',now()-interval '160 day',NULL,'system-seed'),
  ('KINTEX','bs-live-16','e-2026-live','H1','booth-live-16','L-116','sold',      4791600,18,NULL,NULL,'나노소재랩',        'demo-organizer',now()-interval '145 day',NULL,'system-seed'),
  ('KINTEX','bs-live-17','e-2026-live','H1','booth-live-17','L-117','sold',      6534000,18,NULL,NULL,'비전AI시스템',      'demo-organizer',now()-interval '115 day',NULL,'system-seed'),
  ('KINTEX','bs-live-18','e-2026-live','H1','booth-live-18','L-118','sold',      4791600,18,NULL,NULL,'한국물류로보틱스',  'demo-organizer',now()-interval '95 day',NULL,'system-seed'),
  ('KINTEX','bs-live-19','e-2026-live','H1','booth-live-19','L-119','sold',      4356000,18,NULL,NULL,'메디컬디바이스코',  'demo-organizer',now()-interval '85 day',NULL,'system-seed'),
  ('KINTEX','bs-live-20','e-2026-live','H1','booth-live-20','L-120','sold',      4356000,18,NULL,NULL,'클린테크환경',      'demo-organizer',now()-interval '70 day',NULL,'system-seed'),
  ('KINTEX','bs-live-21','e-2026-live','H1','booth-live-21','L-121','sold',      4791600,18,NULL,NULL,'디지털트윈랩',      'demo-organizer',now()-interval '55 day',NULL,'system-seed'),
  ('KINTEX','bs-live-22','e-2026-live','H1','booth-live-22','L-122','sold',      4356000,18,NULL,NULL,'정밀계측기술',      'demo-organizer',now()-interval '40 day',NULL,'system-seed'),
  ('KINTEX','bs-live-23','e-2026-live','H1','booth-live-23','L-123','held',      NULL,   18,'demo-exhibitor',now()+interval '2 day',NULL,NULL,NULL,NULL,'system-seed'),
  ('KINTEX','bs-live-24','e-2026-live','H1','booth-live-24','L-124','sold',      4356000,18,NULL,NULL,'스마트팜테크',      'demo-organizer',now()-interval '15 day',NULL,'system-seed'),
  ('KINTEX','bs-live-25','e-2026-live','H1','booth-live-25','L-125','sold',      6534000,18,NULL,NULL,'3D프린팅솔루션',    'demo-organizer',now()-interval '8 day',NULL,'system-seed'),
  ('KINTEX','bs-live-26','e-2026-live','H1','booth-live-26','L-126','held',      NULL,   18,'demo-exhibitor',now()+interval '3 day',NULL,NULL,NULL,NULL,'system-seed')
ON CONFLICT (event_id, booth_id) DO NOTHING;


-- 4) 설계안(design_plan) — 승인/검토/초안 분포(참가업체 designStatus·대시보드 designApproved).
INSERT INTO design_plan (id, booth_id, version, status, spec) VALUES
  ('dp-live-09','booth-live-09',1,'approved', '{"theme":"island-premium","height":4.0}'::jsonb),
  ('dp-live-10','booth-live-10',1,'approved', '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-11','booth-live-11',1,'approved', '{"theme":"independent","height":3.5}'::jsonb),
  ('dp-live-12','booth-live-12',1,'review',   '{"theme":"corner","height":3.5}'::jsonb),
  ('dp-live-13','booth-live-13',1,'approved', '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-14','booth-live-14',1,'approved', '{"theme":"island-premium","height":4.0}'::jsonb),
  ('dp-live-15','booth-live-15',1,'review',   '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-16','booth-live-16',1,'approved', '{"theme":"independent","height":3.5}'::jsonb),
  ('dp-live-17','booth-live-17',1,'approved', '{"theme":"island-premium","height":4.0}'::jsonb),
  ('dp-live-18','booth-live-18',1,'draft',    '{"theme":"corner","height":3.5}'::jsonb),
  ('dp-live-19','booth-live-19',1,'review',   '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-20','booth-live-20',1,'approved', '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-21','booth-live-21',1,'draft',    '{"theme":"independent","height":3.5}'::jsonb),
  ('dp-live-22','booth-live-22',1,'review',   '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-24','booth-live-24',1,'approved', '{"theme":"assembled-basic","height":3.0}'::jsonb),
  ('dp-live-25','booth-live-25',1,'approved', '{"theme":"island-premium","height":4.0}'::jsonb)
ON CONFLICT (booth_id, version) DO NOTHING;


-- 5) 청구(invoice) 8건 — 입금(paid)/발행(invoiced)/연체(overdue)/대기(pending) 분포.
--    UNIQUE(event_id, exhibitor_name) 멱등. outstanding = total - paid_amount (참가업체 미수금 컬럼).
INSERT INTO invoice (id, event_id, exhibitor_id, exhibitor_name, invoice_no, category, source_ref,
                     subtotal, vat, total, paid_amount, status, due_date, issued_at, paid_at,
                     cancelled_at, cancel_reason, line_items, note, created_by) VALUES
  ('inv-live-09','e-2026-live',NULL,'(주)오토닉스로보','LIVE-2026-0109','rental',NULL,
     6534000,653400,7187400,7187400,'paid', CURRENT_DATE - 3, now()-interval '40 day', now()-interval '12 day', NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 아일랜드 · L-109)","quantity":18,"unitPrice":363000,"amount":6534000}]'::jsonb,'완납.', 'system-seed'),
  ('inv-live-10','e-2026-live',NULL,'한성정밀기계','LIVE-2026-0110','rental',NULL,
     4356000,435600,4791600,4791600,'paid', CURRENT_DATE - 2, now()-interval '35 day', now()-interval '9 day', NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 조립 · L-110)","quantity":18,"unitPrice":242000,"amount":4356000}]'::jsonb,'완납.', 'system-seed'),
  ('inv-live-11','e-2026-live',NULL,'대성전자','LIVE-2026-0111','rental',NULL,
     4791600,479160,5270760,2108304,'invoiced', CURRENT_DATE + 6, now()-interval '30 day', NULL, NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 독립 · L-111)","quantity":18,"unitPrice":266200,"amount":4791600}]'::jsonb,'계약금 40% 수납.', 'system-seed'),
  ('inv-live-14','e-2026-live',NULL,'퓨처모빌리티','LIVE-2026-0114','rental',NULL,
     6534000,653400,7187400,7187400,'paid', CURRENT_DATE - 1, now()-interval '20 day', now()-interval '5 day', NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 아일랜드 · L-114)","quantity":18,"unitPrice":363000,"amount":6534000}]'::jsonb,'완납.', 'system-seed'),
  ('inv-live-16','e-2026-live',NULL,'나노소재랩','LIVE-2026-0116','rental',NULL,
     4791600,479160,5270760,0,'overdue', CURRENT_DATE - 4, now()-interval '25 day', NULL, NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 독립 · L-116)","quantity":18,"unitPrice":266200,"amount":4791600}]'::jsonb,'납부 기한 경과 — 독촉 대상.', 'system-seed'),
  ('inv-live-17','e-2026-live',NULL,'비전AI시스템','LIVE-2026-0117','rental',NULL,
     6534000,653400,7187400,2874960,'invoiced', CURRENT_DATE + 9, now()-interval '15 day', NULL, NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 아일랜드 · L-117)","quantity":18,"unitPrice":363000,"amount":6534000}]'::jsonb,'계약금 40% 수납.', 'system-seed'),
  ('inv-live-20','e-2026-live',NULL,'클린테크환경','LIVE-2026-0120','rental',NULL,
     4356000,435600,4791600,0,'pending', CURRENT_DATE + 12, NULL, NULL, NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 조립 · L-120)","quantity":18,"unitPrice":242000,"amount":4356000}]'::jsonb,'발행 대기.', 'system-seed'),
  ('inv-live-25','e-2026-live',NULL,'3D프린팅솔루션','LIVE-2026-0125','rental',NULL,
     6534000,653400,7187400,7187400,'paid', CURRENT_DATE - 1, now()-interval '8 day', now()-interval '2 day', NULL,NULL,
     '[{"category":"RENTAL","description":"부스 임대료 (18㎡ 아일랜드 · L-125)","quantity":18,"unitPrice":363000,"amount":6534000}]'::jsonb,'완납.', 'system-seed')
ON CONFLICT (id) DO NOTHING;


-- 6) 수납(invoice_payment) — 완납/부분수납 반영(정산 폐루프 정합).
INSERT INTO invoice_payment (id, invoice_id, amount, method, memo, recorded_by, recorded_at) VALUES
  ('pay-live-09-1','inv-live-09', 7187400, 'transfer', '일시 완납',  'system-seed', now()-interval '12 day'),
  ('pay-live-10-1','inv-live-10', 4791600, 'transfer', '일시 완납',  'system-seed', now()-interval '9 day'),
  ('pay-live-11-1','inv-live-11', 2108304, 'transfer', '계약금 40%', 'system-seed', now()-interval '28 day'),
  ('pay-live-14-1','inv-live-14', 7187400, 'transfer', '일시 완납',  'system-seed', now()-interval '5 day'),
  ('pay-live-17-1','inv-live-17', 2874960, 'transfer', '계약금 40%', 'system-seed', now()-interval '14 day'),
  ('pay-live-25-1','inv-live-25', 7187400, 'transfer', '일시 완납',  'system-seed', now()-interval '2 day')
ON CONFLICT (id) DO NOTHING;


-- ============================================================================
-- 검증(수동):
--   -- 참가업체 수: SELECT count(*) FROM booth b JOIN layout l ON l.id=b.layout_id
--   --   WHERE l.event_id='e-2026-live' AND b.assigned_company_name IS NOT NULL; -- >=24
--   -- 월별 추이 버킷: SELECT to_char(created_at,'YYYY-MM'), count(*) FROM booth b
--   --   JOIN layout l ON l.id=b.layout_id WHERE l.event_id='e-2026-live'
--   --   GROUP BY 1 ORDER BY 1; -- 다월 분포
--   -- 입금(paid) 수: SELECT count(*) FROM invoice WHERE event_id='e-2026-live' AND status='paid';
-- ============================================================================
