-- Migration 105: Explicit price availability state
--
-- Public price verification remains strict: price_verified requires a real
-- source-backed price observation. Quote-only/OEM/no-public-price cases are
-- represented separately so TIP can close research loops without inventing
-- prices.

ALTER TABLE transceivers
  ADD COLUMN IF NOT EXISTS price_status VARCHAR(32) NOT NULL DEFAULT 'unknown',
  ADD COLUMN IF NOT EXISTS price_status_updated_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS price_unavailable_verified_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS price_unavailable_reason TEXT;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'transceivers_price_status_check'
  ) THEN
    ALTER TABLE transceivers DROP CONSTRAINT transceivers_price_status_check;
  END IF;

  ALTER TABLE transceivers
    ADD CONSTRAINT transceivers_price_status_check
    CHECK (price_status IN (
      'unknown',
      'public_price',
      'no_public_price',
      'needs_research',
      'ambiguous'
    ));
END $$;

UPDATE transceivers
SET price_status = CASE
      WHEN price_verified = true THEN 'public_price'
      WHEN price_status = 'unknown' THEN 'needs_research'
      ELSE price_status
    END,
    price_status_updated_at = COALESCE(price_status_updated_at, NOW())
WHERE price_status IS NULL
   OR price_status = 'unknown'
   OR price_verified = true;

CREATE INDEX IF NOT EXISTS idx_transceivers_price_status
  ON transceivers (price_status);

CREATE INDEX IF NOT EXISTS idx_transceivers_no_public_price
  ON transceivers (price_unavailable_verified_at)
  WHERE price_status = 'no_public_price';

ALTER TABLE transceiver_verification_evidence
  DROP CONSTRAINT IF EXISTS transceiver_verification_evidence_verification_type_check;

ALTER TABLE transceiver_verification_evidence
  ADD CONSTRAINT transceiver_verification_evidence_verification_type_check
  CHECK (
    verification_type::text = ANY (
      ARRAY[
        'price',
        'price_unavailable',
        'image',
        'details',
        'competitor_match',
        'competitor_no_match',
        'competitor_ambiguous',
        'artifact_quarantine'
      ]
    )
  );

COMMENT ON COLUMN transceivers.price_status IS
  'Resolution state for price evidence: public_price, no_public_price, needs_research, ambiguous, unknown. price_verified remains true only for real public price observations.';