-- Create leads table for FitCoach Pro lead management
CREATE TABLE IF NOT EXISTS leads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  fitness_goal VARCHAR(20) NOT NULL CHECK (
    fitness_goal IN ('abnehmen', 'muskelaufbau', 'ausdauer', 'flexibilitaet')
  ),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ
);

-- Indexes
CREATE UNIQUE INDEX idx_leads_email_active ON leads (email) WHERE deleted_at IS NULL;
CREATE INDEX idx_leads_fitness_goal ON leads (fitness_goal) WHERE deleted_at IS NULL;
CREATE INDEX idx_leads_created_at ON leads (created_at DESC) WHERE deleted_at IS NULL;

-- Auto-update updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_leads_updated_at
  BEFORE UPDATE ON leads
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();