-- Sales Playbook Sections
-- Organize sales content: openers, discovery questions, value props, closes, voicemail scripts, objection handlers

CREATE TABLE IF NOT EXISTS playbook_sections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  category TEXT NOT NULL, -- 'opener', 'discovery', 'value_prop', 'close', 'voicemail', 'objection_handler'
  name TEXT NOT NULL,
  content TEXT NOT NULL,
  lead_type TEXT, -- 'worker', 'contractor', NULL for all
  stage TEXT, -- funnel stage when applicable
  effectiveness_score INTEGER DEFAULT 0, -- Calculated from success rate
  times_used INTEGER DEFAULT 0,
  times_successful INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_playbook_sections_category ON playbook_sections(category);
CREATE INDEX IF NOT EXISTS idx_playbook_sections_lead_type ON playbook_sections(lead_type);
CREATE INDEX IF NOT EXISTS idx_playbook_sections_active ON playbook_sections(is_active) WHERE is_active = true;

-- Function to calculate effectiveness score
CREATE OR REPLACE FUNCTION calculate_effectiveness_score(used INTEGER, successful INTEGER)
RETURNS INTEGER AS $$
BEGIN
  IF used = 0 THEN
    RETURN 0;
  END IF;
  RETURN ROUND((successful::NUMERIC / used::NUMERIC) * 100)::INTEGER;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Trigger to update effectiveness score on times_used/times_successful change
CREATE OR REPLACE FUNCTION update_playbook_section_score()
RETURNS TRIGGER AS $$
BEGIN
  NEW.effectiveness_score := calculate_effectiveness_score(NEW.times_used, NEW.times_successful);
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_playbook_section_score ON playbook_sections;
CREATE TRIGGER trigger_update_playbook_section_score
  BEFORE UPDATE OF times_used, times_successful ON playbook_sections
  FOR EACH ROW
  EXECUTE FUNCTION update_playbook_section_score();

-- RLS policies
ALTER TABLE playbook_sections ENABLE ROW LEVEL SECURITY;

CREATE POLICY "playbook_sections_authenticated_read"
  ON playbook_sections FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "playbook_sections_authenticated_write"
  ON playbook_sections FOR INSERT TO authenticated
  WITH CHECK (true);

CREATE POLICY "playbook_sections_authenticated_update"
  ON playbook_sections FOR UPDATE TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "playbook_sections_authenticated_delete"
  ON playbook_sections FOR DELETE TO authenticated
  USING (true);

CREATE POLICY "playbook_sections_service_all"
  ON playbook_sections FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed data with proven sales content
INSERT INTO playbook_sections (category, name, content, lead_type) VALUES
  -- Openers (for workers)
  ('opener', 'Direct intro', 'Hey mate, it''s Rocky from RateRight. We connect quality tradies with jobs - contractors pay a flat $50 per hire - workers keep 100%. Got a minute?', 'worker'),
  ('opener', 'Referral angle', 'G''day, one of the other {trade} boys mentioned you might be looking for more work. Rocky from RateRight here - reckon I can help.', 'worker'),
  ('opener', 'Shortage angle', 'Hey mate, Rocky from RateRight. We''ve got contractors crying out for {trade} guys in your area. You looking for work?', 'worker'),

  -- Openers (for contractors)
  ('opener', 'Direct intro contractor', 'Hey, Rocky from RateRight here. We help contractors find quality tradies - completely free to use. Got tradies giving you grief?', 'contractor'),
  ('opener', 'Problem focused', 'G''day mate, hearing a lot of contractors struggling to find reliable {trade} guys. Rocky from RateRight - reckon we can help.', 'contractor'),

  -- Discovery questions
  ('discovery', 'Current work situation', 'So what''s your work situation like at the moment? Busy, quiet, somewhere in between?', NULL),
  ('discovery', 'Finding work challenges', 'What''s the hardest part about finding good jobs right now for you?', 'worker'),
  ('discovery', 'Finding tradies challenges', 'What''s been your biggest headache when it comes to finding reliable tradies?', 'contractor'),
  ('discovery', 'Ideal client/job', 'If you could pick your perfect job, what would it look like? Location, type of work, size?', 'worker'),
  ('discovery', 'Previous platforms', 'Have you tried any other platforms or services to find work/tradies? What worked, what didn''t?', NULL),

  -- Value propositions
  ('value_prop', 'No upfront cost worker', 'The beauty of RateRight is you don''t pay a cent. Ever. Workers keep 100% of their rate. The contractor pays a flat $50 hire fee - you pay nothing.', 'worker'),
  ('value_prop', 'Free for contractors', 'Best part? It''s completely free for contractors. You only pay the tradie directly - we don''t take a cut from your end.', 'contractor'),
  ('value_prop', 'Quality tradies', 'We vet every tradie on the platform. You''re not going to get ghosted or have someone rock up who doesn''t know what they''re doing.', 'contractor'),
  ('value_prop', 'Jobs in your area', 'We''ve got contractors specifically in your area looking for {trade} guys. Real jobs, not tyre kickers.', 'worker'),

  -- Closes
  ('close', 'Soft close worker', 'Look, why don''t you sign up, have a look around - costs you nothing. If you see a job you like, put your hand up. Simple as that.', 'worker'),
  ('close', 'Soft close contractor', 'Tell you what, sign up for free, post a job, see what comes back. If the tradies aren''t any good, no harm done. Fair?', 'contractor'),
  ('close', 'Direct close', 'Ready to give it a crack? I can get you signed up in 2 minutes.', NULL),
  ('close', 'Urgency close', 'We''ve got a few jobs going right now that would suit you. Want me to put you forward?', 'worker'),

  -- Objection handlers
  ('objection_handler', 'Already busy', 'That''s actually the best time to sign up - line up work for when things slow down. What tradies tell me is feast or famine, yeah?', 'worker'),
  ('objection_handler', 'Too expensive', 'I get that. But here''s the thing - workers pay absolutely nothing. Zero. The contractor pays a flat $50 per hire. You keep 100% of your rate.', 'worker'),
  ('objection_handler', 'Bad experience with platforms', 'Yeah, I hear that a lot. That''s exactly why we do things differently - we actually vet the tradies/jobs. Not just anyone can sign up.', NULL),
  ('objection_handler', 'Need to think about it', 'No worries at all. What specifically did you want to have a think about? Maybe I can clear something up now.', NULL),

  -- Voicemail scripts
  ('voicemail', 'First contact VM', 'G''day mate, Rocky from RateRight here. We connect tradies with contractors in your area - just had a few jobs come through that might suit you. Give us a bell back when you get a chance - look forward to chatting.', 'worker'),
  ('voicemail', 'Follow up VM', 'Hey mate, Rocky again from RateRight. Just following up on that message I left. Got some work going in your area if you''re interested. Cheers.', 'worker'),
  ('voicemail', 'Contractor first VM', 'G''day, Rocky from RateRight here. We help contractors like yourself find quality tradies - completely free to use. Give us a call back when you get a sec.', 'contractor')
ON CONFLICT DO NOTHING;
