CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

DROP TABLE IF EXISTS credit_ledger CASCADE;
DROP TABLE IF EXISTS checkins CASCADE;
DROP TABLE IF EXISTS vendor_locations CASCADE;
DROP TABLE IF EXISTS vendors CASCADE;
DROP TABLE IF EXISTS player_profiles CASCADE;
DROP TABLE IF EXISTS seasons CASCADE;
DROP TABLE IF EXISTS zones CASCADE;
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    phone VARCHAR(30) UNIQUE NOT NULL,
    email VARCHAR(255),
    password_hash TEXT,
    role VARCHAR(30) NOT NULL CHECK (role IN ('player', 'vendor', 'staff', 'ops', 'admin')),
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE zones (
    zone_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    city VARCHAR(100) NOT NULL,
    status VARCHAR(30) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE seasons (
    season_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    code VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(150) NOT NULL,
    status VARCHAR(30) DEFAULT 'upcoming',
    starts_at TIMESTAMPTZ,
    ends_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE player_profiles (
    player_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID UNIQUE NOT NULL REFERENCES users(user_id),
    display_name VARCHAR(150) NOT NULL,
    rank VARCHAR(50) DEFAULT 'Rookie',
    lifetime_checkins INT DEFAULT 0,
    lifetime_purchases INT DEFAULT 0,
    lifetime_credits_earned INT DEFAULT 0,
    seasons_played INT DEFAULT 0,
    hall_of_fame_count INT DEFAULT 0,
    global_status VARCHAR(30) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE vendors (
    vendor_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    owner_user_id UUID REFERENCES users(user_id),
    business_name VARCHAR(200) NOT NULL,
    tier VARCHAR(30) DEFAULT 'listed',
    status VARCHAR(30) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE vendor_locations (
    location_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    vendor_id UUID NOT NULL REFERENCES vendors(vendor_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    name VARCHAR(200) NOT NULL,
    address TEXT NOT NULL,
    latitude NUMERIC(10,7),
    longitude NUMERIC(10,7),
    radius_meters INT DEFAULT 100,
    qr_token VARCHAR(100) UNIQUE NOT NULL,
    status VARCHAR(30) DEFAULT 'active',
    vendor_reputation_score NUMERIC(5,2) DEFAULT 0,
    founding_vendor BOOLEAN DEFAULT FALSE,
    minimum_reward_stock_compliant BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE checkins (
    checkin_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),
    action_type VARCHAR(30),
    status VARCHAR(30) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE credit_ledger (
    ledger_txn_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),
    type VARCHAR(50) NOT NULL,
    delta INT NOT NULL,
    balance_after INT NOT NULL,
    ref_type VARCHAR(50),
    ref_id UUID,
    reason TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);