-- Competitor Tracking System
-- Track competitor mentions, battle cards, and competitive intel

CREATE TABLE IF NOT EXISTS competitors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  website TEXT,
  description TEXT,
  strengths TEXT[] DEFAULT '{}', -- Array of strength points
  weaknesses TEXT[] DEFAULT '{}', -- Array of weakness points
  pricing_info TEXT,
  battle_card JSONB DEFAULT '{}', -- Full battle card content
  is_active BOOLEAN DEFAULT true,
  mention_count INTEGER DEFAULT 0, -- Denormalized count
  last_mentioned_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS competitor_mentions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  competitor_id UUID REFERENCES competitors(id) ON DELETE CASCADE,
  lead_id UUID REFERENCES leads(id) ON DELETE SET NULL,
  communication_id UUID REFERENCES communications(id) ON DELETE SET NULL,
  mentioned_by UUID, -- User who recorded the mention
  mention_type TEXT NOT NULL, -- 'call', 'sms', 'note', 'research'
  context TEXT, -- What was said
  sentiment TEXT DEFAULT 'neutral', -- 'positive', 'negative', 'neutral'
  outcome TEXT, -- 'won', 'lost', 'pending'
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_competitors_name ON competitors(name);
CREATE INDEX IF NOT EXISTS idx_competitors_active ON competitors(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_competitor ON competitor_mentions(competitor_id);
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_lead ON competitor_mentions(lead_id);
CREATE INDEX IF NOT EXISTS idx_competitor_mentions_created ON competitor_mentions(created_at DESC);

-- Trigger to update mention count and last_mentioned_at
CREATE OR REPLACE FUNCTION update_competitor_mention_stats()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE competitors
    SET mention_count = mention_count + 1,
        last_mentioned_at = NEW.created_at,
        updated_at = NOW()
    WHERE id = NEW.competitor_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE competitors
    SET mention_count = GREATEST(0, mention_count - 1),
        updated_at = NOW()
    WHERE id = OLD.competitor_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_competitor_mention_stats ON competitor_mentions;
CREATE TRIGGER trigger_update_competitor_mention_stats
  AFTER INSERT OR DELETE ON competitor_mentions
  FOR EACH ROW
  EXECUTE FUNCTION update_competitor_mention_stats();

-- RLS policies
ALTER TABLE competitors ENABLE ROW LEVEL SECURITY;
ALTER TABLE competitor_mentions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "competitors_authenticated_all"
  ON competitors FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "competitors_service_all"
  ON competitors FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "competitor_mentions_authenticated_all"
  ON competitor_mentions FOR ALL TO authenticated
  USING (true) WITH CHECK (true);

CREATE POLICY "competitor_mentions_service_all"
  ON competitor_mentions FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed common competitors in Australian trades space
INSERT INTO competitors (name, description, strengths, weaknesses, pricing_info, battle_card) VALUES
  ('Hipages', 'Large Australian trade services marketplace',
   ARRAY['Brand recognition', 'Large customer base', 'Established platform'],
   ARRAY['High commission rates', 'Competitive bidding', 'Job quality varies'],
   '20-30% commission on jobs, monthly subscription options',
   '{"key_objection": "Hipages is more expensive and jobs are competitive", "our_advantage": "$50 flat fee per hire, no subscription fees", "talk_track": "A lot of tradies come to us frustrated with Hipages rates. We charge a flat $50 per hire - no upfront costs, no subscriptions, no percentage cuts."}'::jsonb),

  ('Airtasker', 'General task marketplace including trades',
   ARRAY['Broad task categories', 'Consumer awareness', 'Mobile app'],
   ARRAY['Not trades-focused', 'Race to bottom on pricing', 'Unvetted providers'],
   'Variable commission, typically 15-20%',
   '{"key_objection": "I already use Airtasker", "our_advantage": "We focus on quality trades only - no handymen undercutting professionals", "talk_track": "Airtasker is great for odd jobs but for proper trade work, you want a platform that understands the industry. We only work with qualified tradies."}'::jsonb),

  ('ServiceSeeking', 'Trade services lead generation',
   ARRAY['Quality leads', 'Good matching'],
   ARRAY['Pay per lead model', 'Lead quality varies', 'Expensive for tradies'],
   'Pay per lead, $10-50 per lead depending on job type',
   '{"key_objection": "ServiceSeeking has better leads", "our_advantage": "You only pay when you get paid - no paying for leads that go nowhere", "talk_track": "With us, you dont pay a cent until the jobs done and youve been paid. No more wasting money on leads that dont convert."}'::jsonb),

  ('Word of Mouth', 'Traditional referrals from existing customers',
   ARRAY['Free', 'High trust', 'Warm leads'],
   ARRAY['Unreliable volume', 'Feast or famine', 'Hard to scale'],
   'Free',
   '{"key_objection": "I get all my work from referrals", "our_advantage": "We complement referrals - fill gaps when things are quiet", "talk_track": "Referrals are gold, no doubt. We just help fill the gaps when the phones not ringing. Think of us as backup work."}'::jsonb),

  ('Direct Advertising', 'Google Ads, Facebook Ads, local advertising',
   ARRAY['Control over messaging', 'Brand building'],
   ARRAY['Expensive', 'Requires marketing expertise', 'Time consuming'],
   'Variable, often $500-2000/month',
   '{"key_objection": "I do my own advertising", "our_advantage": "No ad spend, no marketing needed - we bring the jobs to you", "talk_track": "Advertising is expensive and time consuming. We bring you qualified leads without you having to spend on ads or manage campaigns."}'::jsonb)
ON CONFLICT (name) DO NOTHING;
