-- Migration: Add scout_opportunity and scout_claim tables
-- Date: 2026-03-04
-- Issue: #44
-- Purpose: Support the scouting feature where players can scout cards
--          from other teams' opened packs within a 30-minute window.
--
-- Run on dev first, verify with:
--   SELECT count(*) FROM scout_opportunity;
--   SELECT count(*) FROM scout_claim;
--
-- Rollback: See DROP statements at bottom of file

-- ============================================
-- FORWARD MIGRATION
-- ============================================

BEGIN;

CREATE TABLE IF NOT EXISTS scout_opportunity (
    id SERIAL PRIMARY KEY,
    pack_id INTEGER REFERENCES pack(id) ON DELETE SET NULL,
    opener_team_id INTEGER NOT NULL REFERENCES team(id) ON DELETE CASCADE,
    card_ids VARCHAR(255) NOT NULL,        -- JSON array of card IDs, e.g. "[10, 11, 12]"
    expires_at BIGINT NOT NULL,            -- Unix ms timestamp, 30 min after creation
    created BIGINT NOT NULL                -- Unix ms timestamp
);

CREATE TABLE IF NOT EXISTS scout_claim (
    id SERIAL PRIMARY KEY,
    scout_opportunity_id INTEGER NOT NULL REFERENCES scout_opportunity(id) ON DELETE CASCADE,
    card_id INTEGER NOT NULL REFERENCES card(id) ON DELETE CASCADE,
    claimed_by_team_id INTEGER NOT NULL REFERENCES team(id) ON DELETE CASCADE,
    created BIGINT NOT NULL                -- Unix ms timestamp, auto-set on creation
);

-- Unique constraint: one claim per team per opportunity
CREATE UNIQUE INDEX IF NOT EXISTS scout_claim_opportunity_team_uniq
    ON scout_claim (scout_opportunity_id, claimed_by_team_id);

-- Index for the common query: find unclaimed, expired opportunities
CREATE INDEX IF NOT EXISTS scout_opportunity_expires_at_idx
    ON scout_opportunity (expires_at);

COMMIT;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================
-- \d scout_opportunity
-- \d scout_claim
-- SELECT indexname FROM pg_indexes WHERE tablename IN ('scout_opportunity', 'scout_claim');

-- ============================================
-- ROLLBACK (if needed)
-- ============================================
-- DROP TABLE IF EXISTS scout_claim CASCADE;
-- DROP TABLE IF EXISTS scout_opportunity CASCADE;