-- V114: 시상 환수 (award_ledger 컬럼 추가 + status 확장)
--       settling_support_ledger(V90) 패턴 미러:
--         clawback_amount NUMERIC(15,2) NOT NULL DEFAULT 0
--         net_amount      NUMERIC(15,2) NOT NULL DEFAULT 0
--       기존 행 백필: 환수 없던 과거 데이터는 net = award
--       status CHECK에 'CLAWED_BACK' 추가 (기존 제약은 DO 블록으로 동적 탐색 후 DROP)
--       공통코드: AWARD_STATUS 그룹이 V92/V96/V105 어디에도 없으므로 INSERT 생략

-- ============================================================
-- 1. 컬럼 추가
-- ============================================================
ALTER TABLE award_ledger
    ADD COLUMN clawback_amount NUMERIC(15,2) NOT NULL DEFAULT 0,
    ADD COLUMN net_amount      NUMERIC(15,2) NOT NULL DEFAULT 0;

-- ============================================================
-- 2. 기존 행 백필: 환수 없던 과거 = net = award
-- ============================================================
UPDATE award_ledger
   SET net_amount = award_amount
 WHERE net_amount = 0
   AND clawback_amount = 0;

-- ============================================================
-- 3. clawback 음수 방지 제약
-- ============================================================
ALTER TABLE award_ledger
    ADD CONSTRAINT chk_award_clawback CHECK (clawback_amount >= 0);

-- ============================================================
-- 4. status CHECK 재생성 — CLAWED_BACK 추가
--    기존 인라인 CHECK 이름을 추정하지 않고 pg_constraint에서 동적 조회 후 DROP
-- ============================================================
DO $$
DECLARE
    c text;
BEGIN
    SELECT conname INTO c
      FROM pg_constraint
     WHERE conrelid = 'award_ledger'::regclass
       AND contype  = 'c'
       AND pg_get_constraintdef(oid) ILIKE '%status%';
    IF c IS NOT NULL THEN
        EXECUTE 'ALTER TABLE award_ledger DROP CONSTRAINT ' || quote_ident(c);
    END IF;
END $$;

ALTER TABLE award_ledger
    ADD CONSTRAINT chk_award_status
    CHECK (status IN ('CALCULATED','CONFIRMED','PAID','CANCELLED','CLAWED_BACK'));

-- ============================================================
-- 5. 컬럼 코멘트
-- ============================================================
COMMENT ON COLUMN award_ledger.clawback_amount IS '시상 환수액';
COMMENT ON COLUMN award_ledger.net_amount      IS '실지급 시상액 = award_amount − clawback_amount';
