CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS platform_events (
    event_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    event_type VARCHAR(80) NOT NULL,

    actor_user_id UUID NULL REFERENCES users(user_id),
    player_id UUID NULL REFERENCES player_profiles(player_id),
    vendor_id UUID NULL REFERENCES vendors(vendor_id),
    location_id UUID NULL REFERENCES vendor_locations(location_id),
    zone_id UUID NULL REFERENCES zones(zone_id),
    season_id UUID NULL REFERENCES seasons(season_id),

    ref_type VARCHAR(80),
    ref_id UUID,

    severity VARCHAR(30) DEFAULT 'info'
        CHECK (severity IN ('info', 'warning', 'critical')),

    message TEXT NOT NULL,

    metadata JSONB DEFAULT '{}'::jsonb,

    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_platform_events_type
ON platform_events(event_type);

CREATE INDEX IF NOT EXISTS idx_platform_events_zone_season
ON platform_events(zone_id, season_id);

CREATE INDEX IF NOT EXISTS idx_platform_events_created_at
ON platform_events(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_platform_events_player
ON platform_events(player_id);

CREATE INDEX IF NOT EXISTS idx_platform_events_vendor
ON platform_events(vendor_id);

CREATE INDEX IF NOT EXISTS idx_platform_events_severity
ON platform_events(severity);