-- Scripts Command Center Migration
-- Created: Jan 17, 2026
-- Description: Brand guidelines, unified scripts library, and compliance queue

-- =====================================================
-- TABLE 1: Brand Guidelines Configuration
-- =====================================================
CREATE TABLE IF NOT EXISTS brand_guidelines (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Tone & Voice
  tone_description TEXT NOT NULL DEFAULT 'Friendly, direct, Aussie mate vibes. Not corporate.',
  tone_keywords TEXT[] DEFAULT ARRAY['mate', 'hey', 'cheers', 'no worries', 'legend'],

  -- Banned content
  banned_words TEXT[] DEFAULT ARRAY['guaranteed', 'promise', 'best', 'cheapest', 'free'],
  banned_phrases TEXT[] DEFAULT ARRAY['act now', 'limited time', 'don''t miss out', 'once in a lifetime'],

  -- Required elements
  required_signature TEXT DEFAULT 'Cheers, [Name] - RateRight',
  must_include_cta BOOLEAN DEFAULT true,
  max_exclamation_marks INTEGER DEFAULT 1,

  -- Compliance rules
  no_false_claims BOOLEAN DEFAULT true,
  no_pressure_tactics BOOLEAN DEFAULT true,
  must_identify_as_rateright BOOLEAN DEFAULT true,

  -- Versioning
  version INTEGER DEFAULT 1,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT
);

-- =====================================================
-- TABLE 2: Brand Guideline History
-- =====================================================
CREATE TABLE IF NOT EXISTS brand_guideline_history (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  guideline_id UUID REFERENCES brand_guidelines(id),
  changed_by TEXT,
  change_type TEXT, -- 'create', 'update', 'activate'
  old_values JSONB,
  new_values JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- =====================================================
-- TABLE 3: Unified Scripts Library
-- =====================================================
CREATE TABLE IF NOT EXISTS scripts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Basic info
  name VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(50) NOT NULL, -- 'sms', 'call', 'email', 'objection'
  subcategory VARCHAR(50), -- 'cold', 'follow_up', 're_engagement', 'closing', 'nurture'

  -- Targeting
  target_type VARCHAR(20) DEFAULT 'both', -- 'worker', 'contractor', 'both'
  target_stage VARCHAR(50), -- Platform stage this script is for
  target_score_min INTEGER, -- Min lead score
  target_score_max INTEGER, -- Max lead score

  -- Content
  content TEXT NOT NULL, -- The actual script/template
  variables TEXT[], -- ['first_name', 'company', 'sender_name']
  notes TEXT, -- Internal notes for reps

  -- Compliance
  compliance_status VARCHAR(20) DEFAULT 'pending', -- 'approved', 'pending', 'rejected', 'needs_review'
  compliance_checked_at TIMESTAMPTZ,
  compliance_issues JSONB, -- [{issue: 'banned_word', word: 'guaranteed', position: 45}]
  reviewed_by TEXT,

  -- Performance
  times_used INTEGER DEFAULT 0,
  times_replied INTEGER DEFAULT 0,
  reply_rate DECIMAL(5,2) GENERATED ALWAYS AS (
    CASE WHEN times_used > 0 THEN (times_replied::DECIMAL / times_used * 100) ELSE 0 END
  ) STORED,
  avg_sentiment_score DECIMAL(3,2),

  -- Metadata
  is_seed BOOLEAN DEFAULT false, -- True for starter scripts
  is_active BOOLEAN DEFAULT true,
  created_by TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for scripts
CREATE INDEX IF NOT EXISTS idx_scripts_category ON scripts(category, subcategory);
CREATE INDEX IF NOT EXISTS idx_scripts_compliance ON scripts(compliance_status);
CREATE INDEX IF NOT EXISTS idx_scripts_target ON scripts(target_type, target_stage);
CREATE INDEX IF NOT EXISTS idx_scripts_active ON scripts(is_active) WHERE is_active = true;

-- =====================================================
-- TABLE 4: Compliance Review Queue
-- =====================================================
CREATE TABLE IF NOT EXISTS compliance_queue (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Source
  source_type VARCHAR(50) NOT NULL, -- 'ai_draft', 'script', 'manual'
  source_id UUID, -- Reference to script or draft
  lead_id UUID REFERENCES leads(id),

  -- Content
  original_content TEXT NOT NULL,
  suggested_fix TEXT,
  issues JSONB NOT NULL, -- Compliance issues array

  -- Status
  status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'edited'
  reviewed_by TEXT,
  reviewed_at TIMESTAMPTZ,
  final_content TEXT, -- What was actually sent/saved

  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for compliance queue
CREATE INDEX IF NOT EXISTS idx_compliance_queue_status ON compliance_queue(status);
CREATE INDEX IF NOT EXISTS idx_compliance_queue_pending ON compliance_queue(status) WHERE status = 'pending';

-- =====================================================
-- SEED DATA: Initial Brand Guidelines
-- =====================================================
INSERT INTO brand_guidelines (
  tone_description,
  tone_keywords,
  banned_words,
  banned_phrases,
  required_signature,
  must_include_cta,
  max_exclamation_marks,
  is_active,
  created_by
) VALUES (
  'Friendly, direct, Aussie tradie vibes. We''re mates helping mates find work. Not corporate, not salesy. Just real people having real conversations.',
  ARRAY['mate', 'hey', 'cheers', 'no worries', 'legend', 'good on ya', 'too easy'],
  ARRAY['guaranteed', 'promise', 'best', 'cheapest', 'urgent', 'final', 'hurry', 'exclusive'],
  ARRAY['act now', 'limited time', 'don''t miss out', 'once in a lifetime', 'last chance', 'exclusive offer', 'limited spots'],
  'Cheers, {sender_name} - RateRight',
  true,
  1,
  true,
  'system'
) ON CONFLICT DO NOTHING;

-- =====================================================
-- FUNCTION: Update updated_at timestamp
-- =====================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
DROP TRIGGER IF EXISTS update_brand_guidelines_updated_at ON brand_guidelines;
CREATE TRIGGER update_brand_guidelines_updated_at
  BEFORE UPDATE ON brand_guidelines
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_scripts_updated_at ON scripts;
CREATE TRIGGER update_scripts_updated_at
  BEFORE UPDATE ON scripts
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- RLS Policies (if RLS is enabled)
-- =====================================================
-- Enable RLS on new tables
ALTER TABLE brand_guidelines ENABLE ROW LEVEL SECURITY;
ALTER TABLE brand_guideline_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE scripts ENABLE ROW LEVEL SECURITY;
ALTER TABLE compliance_queue ENABLE ROW LEVEL SECURITY;

-- Allow authenticated users to read all
CREATE POLICY "Allow authenticated read brand_guidelines"
  ON brand_guidelines FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Allow authenticated read brand_guideline_history"
  ON brand_guideline_history FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Allow authenticated read scripts"
  ON scripts FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Allow authenticated read compliance_queue"
  ON compliance_queue FOR SELECT
  TO authenticated
  USING (true);

-- Allow authenticated users to insert/update
CREATE POLICY "Allow authenticated insert brand_guidelines"
  ON brand_guidelines FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Allow authenticated update brand_guidelines"
  ON brand_guidelines FOR UPDATE
  TO authenticated
  USING (true);

CREATE POLICY "Allow authenticated insert brand_guideline_history"
  ON brand_guideline_history FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Allow authenticated all scripts"
  ON scripts FOR ALL
  TO authenticated
  USING (true);

CREATE POLICY "Allow authenticated all compliance_queue"
  ON compliance_queue FOR ALL
  TO authenticated
  USING (true);
