-- Nudge Templates Table
-- Stores reusable SMS templates for activation nudges

CREATE TABLE IF NOT EXISTS nudge_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100) NOT NULL,
  stage VARCHAR(50) NOT NULL,  -- 'signed_up', 'profile_complete', 'all', etc.
  lead_type VARCHAR(20) DEFAULT 'all',  -- 'worker', 'contractor', 'all'
  message TEXT NOT NULL,
  variables TEXT[] DEFAULT '{}',  -- ['first_name', 'company', etc.]
  active BOOLEAN DEFAULT true,
  usage_count INTEGER DEFAULT 0,
  last_used_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_nudge_templates_stage ON nudge_templates(stage);
CREATE INDEX IF NOT EXISTS idx_nudge_templates_active ON nudge_templates(active);

-- Add call list fields to leads if not exists
-- These are used by the add-to-call-list feature
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'leads' AND column_name = 'call_list_score') THEN
    ALTER TABLE leads ADD COLUMN call_list_score INTEGER DEFAULT 0;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'leads' AND column_name = 'call_list_reason') THEN
    ALTER TABLE leads ADD COLUMN call_list_reason TEXT;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'leads' AND column_name = 'call_list_reason_type') THEN
    ALTER TABLE leads ADD COLUMN call_list_reason_type VARCHAR(50);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'leads' AND column_name = 'call_list_updated_at') THEN
    ALTER TABLE leads ADD COLUMN call_list_updated_at TIMESTAMPTZ;
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_leads_call_list_score ON leads(call_list_score DESC);

-- Trigger to update updated_at on nudge_templates
CREATE OR REPLACE FUNCTION update_nudge_templates_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS nudge_templates_updated_at ON nudge_templates;
CREATE TRIGGER nudge_templates_updated_at
  BEFORE UPDATE ON nudge_templates
  FOR EACH ROW
  EXECUTE FUNCTION update_nudge_templates_updated_at();
