Database Schema

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT,
  full_name TEXT,
  credits INTEGER DEFAULT 0,
  total_generations INTEGER DEFAULT 0,
  is_master BOOLEAN DEFAULT FALSE,
  role TEXT DEFAULT 'user',
  referral_code TEXT UNIQUE,
  referred_by UUID REFERENCES profiles(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE partners (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  business_name TEXT,
  contact_name TEXT,
  contact_email TEXT,
  user_id UUID REFERENCES profiles(id),
  credits_total INTEGER DEFAULT 0,
  credits_used INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  config JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE clients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT,
  contact_person TEXT,
  phone TEXT,
  credits_total INTEGER DEFAULT 0,
  credits_used INTEGER DEFAULT 0,
  contracted_styles TEXT[] DEFAULT '{}',
  config JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  partner_id UUID REFERENCES partners(id) ON DELETE SET NULL,
  client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
  event_name TEXT NOT NULL,
  event_slug TEXT UNIQUE NOT NULL,
  client_name TEXT,
  client_email TEXT,
  client_access_pin TEXT,
  credits_allocated INTEGER DEFAULT 0,
  credits_used INTEGER DEFAULT 0,
  selected_styles TEXT[] DEFAULT '{}',
  config JSONB DEFAULT '{}'::jsonb,
  start_date TIMESTAMPTZ,
  end_date TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE generations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  event_id UUID REFERENCES events(id) ON DELETE SET NULL,
  model_id TEXT,
  style_id TEXT,
  image_url TEXT NOT NULL,
  aspect_ratio TEXT DEFAULT '9:16',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE api_key_pool (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  api_key TEXT NOT NULL UNIQUE,
  account_name TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  last_used_at TIMESTAMPTZ DEFAULT NOW(),
  usage_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE payment_notifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  mercadopago_id TEXT UNIQUE NOT NULL,
  user_id UUID REFERENCES profiles(id),
  status TEXT,
  credits_added INTEGER,
  amount NUMERIC,
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE styles_metadata (
  id TEXT PRIMARY KEY,
  label TEXT,
  category TEXT,
  subcategory TEXT,
  image_url TEXT,
  tags TEXT[] DEFAULT '{}',
  is_premium BOOLEAN DEFAULT FALSE,
  usage_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE identity_prompts (
  id TEXT PRIMARY KEY,
  master_prompt TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION increment_event_credit(p_event_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
  v_allocated INTEGER;
  v_used INTEGER;
BEGIN
  SELECT credits_allocated, credits_used
  INTO v_allocated, v_used
  FROM events
  WHERE id = p_event_id
  FOR UPDATE;

  IF v_used >= v_allocated THEN
    RETURN FALSE; -- No credits remaining
  END IF;

  UPDATE events
  SET credits_used = credits_used + 1
  WHERE id = p_event_id;

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

const { data: creditOk, error } = await supabase.rpc(
  'increment_event_credit',
  { p_event_id: event_id }
);

if (!creditOk) {
  throw new Error('Event credits exhausted');
}

-- Generations lookup by user
CREATE INDEX idx_generations_user_id ON generations(user_id);

-- Generations lookup by event
CREATE INDEX idx_generations_event_id ON generations(event_id);

-- Event lookup by slug (for QR codes)
CREATE INDEX idx_events_slug ON events(event_slug);

-- Partner's events
CREATE INDEX idx_events_partner_id ON events(partner_id);

-- Client's events
CREATE INDEX idx_events_client_id ON events(client_id);

-- API key pool selection
CREATE INDEX idx_api_key_pool_active_lru 
  ON api_key_pool(is_active, last_used_at);