CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =========================
-- v2.3 ALIGNMENT PATCH
-- =========================

-- player_profiles additions
ALTER TABLE player_profiles
ADD COLUMN IF NOT EXISTS founding_vendor_referral BOOLEAN DEFAULT FALSE;

-- vendor_locations additions
ALTER TABLE vendor_locations
ADD COLUMN IF NOT EXISTS seasons_active INT DEFAULT 0;

-- social anti-spoofing
ALTER TABLE social_submissions
ADD COLUMN IF NOT EXISTS image_hash VARCHAR(64);

CREATE UNIQUE INDEX IF NOT EXISTS ux_social_submissions_image_hash
ON social_submissions(image_hash)
WHERE image_hash IS NOT NULL;

-- ledger duplicate protection
CREATE UNIQUE INDEX IF NOT EXISTS ux_credit_ledger_ref
ON credit_ledger(ref_type, ref_id)
WHERE ref_type IS NOT NULL AND ref_id IS NOT NULL;

-- =========================
-- v2.3 credited visit cap
-- Max 30 credited visits per player/location/season
-- =========================

CREATE TABLE IF NOT EXISTS player_location_season_visits (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    location_id UUID NOT NULL REFERENCES vendor_locations(location_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    credited_visit_count INT DEFAULT 0,
    last_credited_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(player_id, location_id, season_id)
);

-- =========================
-- Clue definitions
-- =========================

CREATE TABLE IF NOT EXISTS clue_definitions (
    clue_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    location_id UUID NOT NULL REFERENCES vendor_locations(location_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    phase INT NOT NULL CHECK (phase IN (1,2,3)),
    vague_text TEXT NOT NULL,
    partial_text TEXT NOT NULL,
    full_text TEXT NOT NULL,
    is_hidden_stop_fragment BOOLEAN DEFAULT FALSE,
    fragment_order INT,
    is_archived BOOLEAN DEFAULT FALSE,
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(location_id, season_id)
);

-- =========================
-- Player earned clues
-- =========================

CREATE TABLE IF NOT EXISTS player_clues (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    clue_id UUID NOT NULL REFERENCES clue_definitions(clue_id),
    stop_id UUID NOT NULL REFERENCES vendor_locations(location_id),
    tier_earned VARCHAR(30) NOT NULL CHECK (tier_earned IN ('checkin','social','purchase')),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    earned_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(player_id, clue_id, tier_earned)
);

-- =========================
-- Phase progress
-- =========================

CREATE TABLE IF NOT EXISTS phase_progress (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    current_phase INT DEFAULT 1 CHECK (current_phase IN (1,2,3)),
    phase1_stops_done INT DEFAULT 0,
    phase2_stops_done INT DEFAULT 0,
    phase3_stops_done INT DEFAULT 0,
    phase1_unlocked_at TIMESTAMPTZ DEFAULT NOW(),
    phase2_unlocked_at TIMESTAMPTZ,
    phase3_unlocked_at TIMESTAMPTZ,
    hidden_stops_found INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(player_id, zone_id, season_id)
);

-- =========================
-- Hall of Fame
-- =========================

CREATE TABLE IF NOT EXISTS platform_hall_of_fame (
    hof_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    display_name VARCHAR(150) NOT NULL,
    final_balance INT NOT NULL,
    position INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(zone_id, season_id, position)
);

-- =========================
-- Referrals
-- =========================

CREATE TABLE IF NOT EXISTS referrals (
    referral_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    referrer_player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    referred_player_id UUID REFERENCES player_profiles(player_id),
    referral_code VARCHAR(20) UNIQUE NOT NULL,
    status VARCHAR(30) DEFAULT 'pending' CHECK (status IN ('pending','credited','expired')),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),
    credited_at TIMESTAMPTZ,
    ledger_txn_id UUID REFERENCES credit_ledger(ledger_txn_id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =========================
-- Helpful indexes
-- =========================

CREATE INDEX IF NOT EXISTS idx_checkins_player_location_season
ON checkins(player_id, location_id, season_id);

CREATE INDEX IF NOT EXISTS idx_credit_ledger_player_zone_season
ON credit_ledger(player_id, zone_id, season_id);

CREATE INDEX IF NOT EXISTS idx_player_clues_player_season
ON player_clues(player_id, season_id);

CREATE INDEX IF NOT EXISTS idx_phase_progress_player_season
ON phase_progress(player_id, season_id);

CREATE INDEX IF NOT EXISTS idx_referrals_code
ON referrals(referral_code);