-- 킨텍스 — M15 공사/장치 옥션(역경매) 스키마 + 시드 (멱등)
-- PLANNING §5 M15: Auction 1─N Bid(=Quotation) ─ Award. 등록업체(company.registered)만 응찰(서버 게이트).
-- 봉인 입찰: 마감 전 경쟁 견적 비공개(서비스 레이어에서 강제 마스킹), 마감 후 발주자만 전체 공개.
-- 보안 불변: 본 마이그레이션은 V16 단독. V1~V13 및 타 팀 마이그레이션(V14/V15/V17~)을 변경하지 않는다.

-- 옥션(공고) ------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auction (
    id                  varchar(40)  PRIMARY KEY,
    event_id            varchar(40)  NOT NULL REFERENCES event(id) ON DELETE CASCADE,
    title               varchar(200) NOT NULL,
    category            varchar(40)  NOT NULL,                    -- 전시디자인설치|구조물 임대|전기/조명|영상/음향|네트워크
    auction_type        varchar(20)  NOT NULL DEFAULT 'reverse',  -- reverse(역경매)|rfq
    award_criteria      varchar(20)  NOT NULL DEFAULT 'comprehensive', -- lowest|comprehensive
    weight_price        integer      NOT NULL DEFAULT 60,
    weight_reputation   integer      NOT NULL DEFAULT 25,
    weight_delivery     integer      NOT NULL DEFAULT 15,
    round               integer      NOT NULL DEFAULT 1,
    deadline            timestamptz  NOT NULL,
    material_package_id varchar(40),                              -- M2~M5 AI 자료 패키지 소프트 참조
    materials           varchar(120) NOT NULL DEFAULT '',         -- 콤마구분: layout,design,boq,aiimage
    accent              varchar(20)  NOT NULL DEFAULT 'primary',
    created_by          varchar(40),
    created_at          timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_auction_event ON auction(event_id);

-- 초대(등록업체만) — 미등록 업체는 초대·응찰 원천 차단 -------------------------
CREATE TABLE IF NOT EXISTS auction_invite (
    id         varchar(40) PRIMARY KEY,
    auction_id varchar(40) NOT NULL REFERENCES auction(id) ON DELETE CASCADE,
    company_id varchar(40) NOT NULL REFERENCES company(id),
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (auction_id, company_id)
);

-- 응찰 = 견적서(Quotation). (auction,company) 당 1행 — 재응찰 시 버전 증가·갱신 --
CREATE TABLE IF NOT EXISTS bid (
    id             varchar(40)  PRIMARY KEY,
    auction_id     varchar(40)  NOT NULL REFERENCES auction(id) ON DELETE CASCADE,
    company_id     varchar(40)  NOT NULL REFERENCES company(id),
    bidder_user_id varchar(40),
    round          integer      NOT NULL DEFAULT 1,
    subtotal       bigint       NOT NULL DEFAULT 0,
    vat            bigint       NOT NULL DEFAULT 0,
    total          bigint       NOT NULL,
    lead_days      integer      NOT NULL DEFAULT 0,
    valid_until    date,
    terms          text,
    lines          jsonb,                                -- 라인아이템(공종·자재·수량·단가) 스냅샷
    version        integer      NOT NULL DEFAULT 1,
    status         varchar(20)  NOT NULL DEFAULT 'submitted',
    submitted_at   timestamptz  NOT NULL DEFAULT now(),
    updated_at     timestamptz  NOT NULL DEFAULT now(),
    UNIQUE (auction_id, company_id)
);
CREATE INDEX IF NOT EXISTS idx_bid_auction ON bid(auction_id);

-- 낙찰(Award) — 옥션당 1건. 감사 추적(사유·발주자·시각) --------------------------
CREATE TABLE IF NOT EXISTS award (
    id         varchar(40) PRIMARY KEY,
    auction_id varchar(40) NOT NULL UNIQUE REFERENCES auction(id) ON DELETE CASCADE,
    bid_id     varchar(40) NOT NULL REFERENCES bid(id),
    reason     text        NOT NULL,
    awarded_by varchar(40),
    awarded_at timestamptz NOT NULL DEFAULT now()
);

-- 업체 평판(종합평가 스코어 입력) — 초기 규정준수 이력·자기신고, 축적 후 시공 평점 --
CREATE TABLE IF NOT EXISTS company_reputation (
    company_id varchar(40)  PRIMARY KEY REFERENCES company(id) ON DELETE CASCADE,
    rating     numeric(3,1) NOT NULL DEFAULT 4.0,   -- 5점 만점
    jobs_done  integer      NOT NULL DEFAULT 0,
    claim_rate numeric(5,2) NOT NULL DEFAULT 0      -- 클레임 발생률(%)
);

-- ===== 시드(멱등) — 기존 event(e-2026-smf)·company(co-000x) 참조 =================

INSERT INTO company_reputation (company_id, rating, jobs_done, claim_rate) VALUES
  ('co-0001', 4.8, 12, 0.0),
  ('co-0002', 4.6, 8,  1.5),
  ('co-0003', 4.2, 15, 3.0),
  ('co-0004', 4.0, 5,  2.0)
ON CONFLICT (company_id) DO NOTHING;

-- 옥션 3건: 진행중(종합)·진행중(최저가)·정산중(낙찰 완료) --------------------------
INSERT INTO auction (id, event_id, title, category, auction_type, award_criteria,
                     weight_price, weight_reputation, weight_delivery, round, deadline,
                     material_package_id, materials, accent) VALUES
  ('AUC-2026-0102', 'e-2026-smf', 'A-102 독립부스 시공', '전시디자인설치', 'reverse', 'comprehensive',
     60, 25, 15, 2, now() + interval '2 day', 'mp-a102', 'layout,design,boq,aiimage', 'primary'),
  ('AUC-2026-0405', 'e-2026-smf', 'B-405 트러스 구조물 설치', '구조물 임대', 'reverse', 'lowest',
     100, 0, 0, 1, now() + interval '1 day', NULL, 'layout,boq', 'secondary'),
  ('AUC-2026-0210', 'e-2026-smf', 'C-10 공용 통로 조명 공사', '전기/조명', 'reverse', 'comprehensive',
     50, 30, 20, 1, now() - interval '3 day', NULL, 'layout,boq', 'tertiary')
ON CONFLICT (id) DO NOTHING;

INSERT INTO auction_invite (id, auction_id, company_id) VALUES
  ('ai-0102-1', 'AUC-2026-0102', 'co-0001'),
  ('ai-0102-2', 'AUC-2026-0102', 'co-0002'),
  ('ai-0102-3', 'AUC-2026-0102', 'co-0003'),
  ('ai-0405-1', 'AUC-2026-0405', 'co-0002'),
  ('ai-0405-2', 'AUC-2026-0405', 'co-0003'),
  ('ai-0210-1', 'AUC-2026-0210', 'co-0003')
ON CONFLICT (auction_id, company_id) DO NOTHING;

INSERT INTO bid (id, auction_id, company_id, round, subtotal, vat, total, lead_days) VALUES
  -- AUC-0102 (진행중·종합) — 최저가 co-0001 8.4M
  ('bid-0102-1', 'AUC-2026-0102', 'co-0001', 2, 8400000, 840000, 8400000, 12),
  ('bid-0102-2', 'AUC-2026-0102', 'co-0002', 2, 8700000, 870000, 8700000, 10),
  ('bid-0102-3', 'AUC-2026-0102', 'co-0003', 1, 8900000, 890000, 8900000, 14),
  -- AUC-0405 (진행중·최저가)
  ('bid-0405-1', 'AUC-2026-0405', 'co-0002', 1, 4250000, 425000, 4250000, 8),
  ('bid-0405-2', 'AUC-2026-0405', 'co-0003', 1, 4600000, 460000, 4600000, 9),
  -- AUC-0210 (정산중·낙찰) — 낙찰 co-0003 12M
  ('bid-0210-1', 'AUC-2026-0210', 'co-0003', 1, 12000000, 1200000, 12000000, 20),
  ('bid-0210-2', 'AUC-2026-0210', 'co-0001', 1, 12800000, 1280000, 12800000, 18)
ON CONFLICT (auction_id, company_id) DO NOTHING;

INSERT INTO award (id, auction_id, bid_id, reason, awarded_by) VALUES
  ('aw-0210', 'AUC-2026-0210', 'bid-0210-1',
   '최저가 응찰 + 최근 3년 유사 규모 시공 실적 우수(종합평가 1위).', 'system-seed')
ON CONFLICT (auction_id) DO NOTHING;
