-- Script System Migration
-- Creates tables for scripts, objections, and session tracking

-- Scripts table
CREATE TABLE IF NOT EXISTS scripts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(200) NOT NULL,
  description TEXT,
  category VARCHAR(50), -- 'cold_call', 'follow_up', 'closing', 'reactivation'
  target_type VARCHAR(20), -- 'worker', 'contractor', 'both'
  target_stage VARCHAR(50), -- platform stage this script is for

  -- Script content
  opening TEXT,
  discovery_questions JSONB DEFAULT '[]',
  value_props JSONB DEFAULT '[]',
  closing_techniques JSONB DEFAULT '[]',

  -- Metadata
  is_active BOOLEAN DEFAULT true,
  times_used INTEGER DEFAULT 0,
  success_count INTEGER DEFAULT 0,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_scripts_category ON scripts(category);
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;

-- Objections table
CREATE TABLE IF NOT EXISTS objections (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  objection TEXT NOT NULL,
  category VARCHAR(50), -- 'price', 'timing', 'trust', 'competition', 'authority', 'need'
  frequency INTEGER DEFAULT 0,
  responses JSONB DEFAULT '[]', -- [{response, effectiveness_score, times_used}]
  tags TEXT[],
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_objections_category ON objections(category);

-- Script usage sessions
CREATE TABLE IF NOT EXISTS script_sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  script_id UUID REFERENCES scripts(id) ON DELETE SET NULL,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  started_at TIMESTAMPTZ DEFAULT NOW(),
  ended_at TIMESTAMPTZ,
  duration_seconds INTEGER,
  outcome VARCHAR(50), -- 'success', 'callback', 'objection', 'not_interested', 'no_answer'
  objections_encountered UUID[],
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_script_sessions_script ON script_sessions(script_id);
CREATE INDEX IF NOT EXISTS idx_script_sessions_lead ON script_sessions(lead_id);
CREATE INDEX IF NOT EXISTS idx_script_sessions_outcome ON script_sessions(outcome);

-- Link table for scripts <-> objections (common objections for each script)
CREATE TABLE IF NOT EXISTS script_objections (
  script_id UUID REFERENCES scripts(id) ON DELETE CASCADE,
  objection_id UUID REFERENCES objections(id) ON DELETE CASCADE,
  frequency INTEGER DEFAULT 0, -- how often this objection comes up in this script
  PRIMARY KEY (script_id, objection_id)
);

-- Insert starter scripts
INSERT INTO scripts (name, description, category, target_type, target_stage, opening, discovery_questions, value_props, closing_techniques) VALUES
(
  'Contractor Cold Call - Stripe Setup',
  'For contractors stuck at stripe_ready stage who need to complete payment setup',
  'reactivation',
  'contractor',
  'stripe_ready',
  'Hey {{first_name}}, this is [Your Name] from RateRight. I noticed you started setting up your account but haven''t finished connecting payments yet. Quick question - are you still looking to hire workers through the platform?',
  '[
    {"question": "What''s been holding you back from finishing the setup?", "purpose": "Identify blocker", "goodAnswers": ["Too busy", "Wasn''t sure how", "Had questions"], "redFlags": ["Changed mind", "Using competitor"]},
    {"question": "When''s your next project starting?", "purpose": "Create urgency", "goodAnswers": ["Soon", "Next week", "This month"], "redFlags": ["No projects planned", "Months away"]},
    {"question": "How many workers do you typically need on a job?", "purpose": "Gauge potential value", "goodAnswers": ["2-5", "5-10", "10+"], "redFlags": ["Just 1", "Not sure"]}
  ]',
  '[
    {"prop": "Takes 2 minutes to connect Stripe, then you can post jobs immediately", "whenToUse": "If blocker is time/complexity", "proofPoints": ["Other contractors did it in under 2 mins", "I can walk you through it right now"]},
    {"prop": "Workers are actively looking for jobs in your area right now", "whenToUse": "If they have upcoming projects", "proofPoints": ["We have X workers in your suburb", "Average response time is under 2 hours"]},
    {"prop": "No fees until you actually hire someone", "whenToUse": "If concerned about cost", "proofPoints": ["Free to post jobs", "Only pay when you find the right worker"]}
  ]',
  '[
    {"name": "Walk-Through Close", "script": "Let me stay on the line while you finish - just go to Settings > Payments and I''ll walk you through it", "whenToUse": "When they''re receptive and have time now"},
    {"name": "Callback Close", "script": "I can call you back in 10 minutes once you''re at your computer - what time works?", "whenToUse": "When they''re interested but busy"},
    {"name": "Urgency Close", "script": "There''s a [trade] looking for work in your area right now - want me to hold them while you finish setup?", "whenToUse": "When you have matching workers available"}
  ]'
),
(
  'Worker Follow-Up - Profile Completion',
  'For workers who signed up but haven''t completed their profile',
  'follow_up',
  'worker',
  'signed_up',
  'Hey {{first_name}}, this is [Your Name] from RateRight. I saw you signed up recently - just wanted to check in and see if you had any questions about getting set up?',
  '[
    {"question": "What type of work are you mainly looking for?", "purpose": "Understand their trade focus", "goodAnswers": ["Specific trade mentioned", "Multiple trades"], "redFlags": ["Not sure", "Just browsing"]},
    {"question": "Are you available to start work this week?", "purpose": "Gauge availability/urgency", "goodAnswers": ["Yes", "Very soon"], "redFlags": ["Not for months", "Just exploring"]},
    {"question": "Do you have your white card / insurance sorted?", "purpose": "Identify completion blockers", "goodAnswers": ["Yes", "Getting it soon"], "redFlags": ["No and no plans to"]}
  ]',
  '[
    {"prop": "Contractors are posting jobs in your area daily", "whenToUse": "If they seem keen but haven''t acted", "proofPoints": ["X jobs posted this week in your trade", "Average hourly rate is $X"]},
    {"prop": "Complete profiles get 3x more job offers", "whenToUse": "If profile is incomplete", "proofPoints": ["Takes 5 minutes to finish", "Add your skills and availability"]},
    {"prop": "We can help you find consistent work", "whenToUse": "If they mention wanting steady income", "proofPoints": ["Many workers get repeat bookings", "Build relationships with contractors"]}
  ]',
  '[
    {"name": "Profile Push", "script": "Let''s get your profile finished right now - what trade should I list as your primary?", "whenToUse": "When they''re engaged and have time"},
    {"name": "Document Reminder", "script": "Once you upload your white card, you''ll be able to apply for jobs straight away. Do you have it handy?", "whenToUse": "When documents are the blocker"},
    {"name": "Job Alert", "script": "I can set you up to get notified when [trade] jobs are posted in [area] - want me to do that now?", "whenToUse": "When building interest"}
  ]'
),
(
  'Contractor Cold Call - New Lead',
  'Initial outreach to contractors who haven''t signed up yet',
  'cold_call',
  'contractor',
  'not_signed_up',
  'Hey {{first_name}}, this is [Your Name] from RateRight. We help contractors like {{company}} find reliable tradies quickly. Do you have 2 minutes?',
  '[
    {"question": "How do you usually find workers when you need them?", "purpose": "Understand current process/pain", "goodAnswers": ["Word of mouth", "It''s hard", "Takes too long"], "redFlags": ["Have a great system", "Don''t need workers"]},
    {"question": "What trades do you need most often?", "purpose": "Match to our supply", "goodAnswers": ["Common trades we have"], "redFlags": ["Very niche trades"]},
    {"question": "What''s your biggest headache when hiring casuals?", "purpose": "Identify pain point to solve", "goodAnswers": ["Reliability", "No-shows", "Finding them fast"], "redFlags": ["No issues"]}
  ]',
  '[
    {"prop": "Find vetted workers in under 24 hours", "whenToUse": "If speed is their pain point", "proofPoints": ["Average time to first applicant is 2 hours", "All workers are verified"]},
    {"prop": "No subscription, only pay when you hire", "whenToUse": "If concerned about cost/commitment", "proofPoints": ["Free to post jobs", "Cancel anytime"]},
    {"prop": "Replace no-shows same day", "whenToUse": "If reliability is their pain", "proofPoints": ["Large pool of available workers", "Backup workers ready to go"]}
  ]',
  '[
    {"name": "Trial Close", "script": "Why don''t you try posting one job and see the response? It''s free to post.", "whenToUse": "When they''re interested but hesitant"},
    {"name": "Demo Close", "script": "I can show you how it works in 2 minutes - want me to share my screen?", "whenToUse": "When they want to see before committing"},
    {"name": "Next Step Close", "script": "I''ll send you a link to sign up - takes 30 seconds. What email should I use?", "whenToUse": "When they''re ready to move forward"}
  ]'
);

-- Insert common objections
INSERT INTO objections (objection, category, responses, tags) VALUES
(
  'I don''t have time right now',
  'timing',
  '[
    {"response": "Totally understand - when would be a better time to chat? I can call you back.", "effectiveness": 70, "timesUsed": 0},
    {"response": "No worries, this will only take 2 minutes. Or I can send you a link to check out when you have time?", "effectiveness": 65, "timesUsed": 0}
  ]',
  ARRAY['common', 'brush-off']
),
(
  'I already have workers / a system',
  'competition',
  '[
    {"response": "That''s great! Most of our contractors use us as a backup for when their regulars aren''t available. Ever had a no-show?", "effectiveness": 75, "timesUsed": 0},
    {"response": "Nice one. What do you do when you need someone urgently and your usual guys are booked?", "effectiveness": 70, "timesUsed": 0}
  ]',
  ARRAY['common', 'satisfied']
),
(
  'How much does it cost?',
  'price',
  '[
    {"response": "Free to sign up and post jobs. You only pay a small fee when you actually hire someone - and you set the rate.", "effectiveness": 80, "timesUsed": 0},
    {"response": "Nothing upfront. Post as many jobs as you want for free. Only pay when you find the right worker.", "effectiveness": 75, "timesUsed": 0}
  ]',
  ARRAY['common', 'buying-signal']
),
(
  'I need to think about it',
  'timing',
  '[
    {"response": "Of course. What specifically would you want to think through? Maybe I can help clarify.", "effectiveness": 70, "timesUsed": 0},
    {"response": "No rush. Want me to send you some info to look over? What''s the best email?", "effectiveness": 65, "timesUsed": 0}
  ]',
  ARRAY['common', 'stall']
),
(
  'I''ve had bad experiences with platforms/apps',
  'trust',
  '[
    {"response": "I hear that a lot actually. What went wrong? We''ve specifically built features to address those issues.", "effectiveness": 75, "timesUsed": 0},
    {"response": "That''s fair. We''re different because [specific differentiator]. But I get it - maybe try one job and see?", "effectiveness": 70, "timesUsed": 0}
  ]',
  ARRAY['common', 'past-experience']
),
(
  'Send me an email / more info',
  'timing',
  '[
    {"response": "Sure, what''s your email? Quick question while I have you - what trade do you need most?", "effectiveness": 60, "timesUsed": 0},
    {"response": "I can do that. Honestly though, signing up takes 30 seconds and you''ll see everything. Want me to send the link instead?", "effectiveness": 65, "timesUsed": 0}
  ]',
  ARRAY['common', 'brush-off']
);
