-- NGO Toolkit Lab — Database Schema
-- Run: psql $DATABASE_URL < lib/db/schema.sql

CREATE TABLE IF NOT EXISTS admin_config (
  key VARCHAR(100) PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS leads (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  org_name VARCHAR(255),
  tool VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);
-- One free generation per email globally
CREATE UNIQUE INDEX IF NOT EXISTS leads_email_unique ON leads(email);

CREATE TABLE IF NOT EXISTS sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tool VARCHAR(50) NOT NULL,
  email VARCHAR(255),
  input JSONB NOT NULL DEFAULT '{}',
  output TEXT,
  is_paid BOOLEAN DEFAULT FALSE,
  payment_id VARCHAR(255),
  improvements_used INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS usage (
  id SERIAL PRIMARY KEY,
  session_id UUID REFERENCES sessions(id),
  tool VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  tokens_used INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Default config
INSERT INTO admin_config (key, value) VALUES
  ('price_currency', 'EUR'),
  ('free_tier_enabled', 'true'),
  ('cap_improve', '10'),
  ('cap_analyse', '3'),
  ('openai_model', 'claude-3-5-sonnet-20241022'),  -- rename key kept for compat, value is Claude model
  ('lemonsqueezy_store_id', ''),
  -- Per-tool prices (cents) and LemonSqueezy variant IDs
  ('price_logframe', '900'),
  ('variant_logframe', ''),
  ('price_funder-rewriter', '900'),
  ('variant_funder-rewriter', ''),
  ('price_smart-indicators', '900'),
  ('variant_smart-indicators', ''),
  ('price_theory-of-change', '900'),
  ('variant_theory-of-change', '')
ON CONFLICT (key) DO NOTHING;

CREATE INDEX IF NOT EXISTS idx_sessions_tool ON sessions(tool);
CREATE INDEX IF NOT EXISTS idx_sessions_email ON sessions(email);
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(email);
CREATE INDEX IF NOT EXISTS idx_usage_session ON usage(session_id);