-- Insert categories
INSERT INTO categories (name) VALUES 
  ('sales performance'),
  ('product'),
  ('marketing');

-- Insert quarter
INSERT INTO quarters (name, start_date, end_date, focus)
VALUES ('Q2 2025 (Mar, Apr, May)', '2025-03-01', '2025-05-31', 'GHL Ecosystem');

-- Get quarter and category IDs
DO $$
DECLARE
  quarter_id INTEGER := (SELECT id FROM quarters WHERE name = 'Q2 2025 (Mar, Apr, May)');
  sales_id INTEGER := (SELECT id FROM categories WHERE name = 'sales performance');
  product_id INTEGER := (SELECT id FROM categories WHERE name = 'product');
  marketing_id INTEGER := (SELECT id FROM categories WHERE name = 'marketing');
  obj1_id INTEGER;
  obj2_id INTEGER;
  obj3_id INTEGER;
  kr_id INTEGER;
BEGIN
  -- Insert Objective #1
  INSERT INTO objectives (title, category_id, quarter_id, status, completion)
  VALUES ('Successfully launch and validate WhatSnap''s market fit within the GoHighLevel ecosystem', 
          sales_id, quarter_id, 'on-track', 65)
  RETURNING id INTO obj1_id;

  -- Insert Key Results for Objective #1
  INSERT INTO key_results (objective_id, title, completion, status)
  VALUES 
    (obj1_id, 'Convert at least 30-50 clients from Wazzap WA Group', 70, 'on-track'),
    (obj1_id, 'Build and activate an affiliate network of 25 GoHighLevel agency partners', 60, 'on-track'),
    (obj1_id, 'Achieve 30% of customer acquisition through affiliate channels', 45, 'at-risk'),
    (obj1_id, 'Establish 3 strategic partnerships with major GoHighLevel influencers', 85, 'on-track');

  -- Insert placeholder actions for each Key Result
  FOR kr_id IN (SELECT id FROM key_results WHERE objective_id = obj1_id) LOOP
    INSERT INTO actions (key_result_id, title, completed)
    VALUES 
      (kr_id, 'Action X placeholder', FALSE),
      (kr_id, 'Action Y placeholder', FALSE),
      (kr_id, 'Action Z placeholder', FALSE);
  END LOOP;

  -- Insert Objective #2
  INSERT INTO objectives (title, category_id, quarter_id, status, completion)
  VALUES ('Build the most user-friendly and reliable WhatsApp AI automation platform that delivers immediate value to GoHighLevel users', 
          product_id, quarter_id, 'at-risk', 55)
  RETURNING id INTO obj2_id;

  -- Insert Key Results for Objective #2
  INSERT INTO key_results (objective_id, title, completion, status)
  VALUES 
    (obj2_id, 'Drive 20% of new users to successfully complete onboarding autonomously', 60, 'on-track'),
    (obj2_id, 'Implement CI/CD pipeline', 90, 'on-track'),
    (obj2_id, 'Launch and validate seamless booking system', 75, 'on-track'),
    (obj2_id, 'Successfully implement bulk messaging system', 40, 'at-risk'),
    (obj2_id, 'Deploy white-label affiliate system', 30, 'off-track'),
    (obj2_id, 'Allowing our users to connect their own API', 45, 'at-risk'),
    (obj2_id, 'Launch "Scenario" system', 60, 'on-track'),
    (obj2_id, 'Implement follow-up system', 55, 'on-track'),
    (obj2_id, 'Successfully implement Answer with audio', 65, 'on-track'),
    (obj2_id, 'Successfully deploy our agents on SMS', 35, 'off-track');

  -- Insert placeholder actions for each Key Result
  FOR kr_id IN (SELECT id FROM key_results WHERE objective_id = obj2_id) LOOP
    INSERT INTO actions (key_result_id, title, completed)
    VALUES 
      (kr_id, 'Action X placeholder', FALSE),
      (kr_id, 'Action Y placeholder', FALSE),
      (kr_id, 'Action Z placeholder', FALSE);
  END LOOP;

  -- Insert Objective #3
  INSERT INTO objectives (title, category_id, quarter_id, status, completion)
  VALUES ('Build a sustainable and profitable growth engine', 
          marketing_id, quarter_id, 'on-track', 70)
  RETURNING id INTO obj3_id;

  -- Insert Key Results for Objective #3
  INSERT INTO key_results (objective_id, title, completion, status)
  VALUES 
    (obj3_id, 'Maintain CAC below $150 per customer', 85, 'on-track'),
    (obj3_id, 'Achieve CAC payback period of < 3 months', 75, 'on-track'),
    (obj3_id, 'Achieve average LTV:CAC ratio of 3:1 (meaning the LTV should be 3x the CAC)', 65, 'on-track'),
    (obj3_id, 'Generate 25 user-created content pieces (testimonials, case studies)', 55, 'at-risk'),
    (obj3_id, 'Achieve 70% of lead qualification through our own agents', 80, 'on-track'),
    (obj3_id, 'Generate 15 AI success stories showcasing ROI for users', 50, 'at-risk'),
    (obj3_id, 'Create a community with at least 300 members', 80, 'on-track');

  -- Insert placeholder actions for each Key Result
  FOR kr_id IN (SELECT id FROM key_results WHERE objective_id = obj3_id) LOOP
    INSERT INTO actions (key_result_id, title, completed)
    VALUES 
      (kr_id, 'Action X placeholder', FALSE),
      (kr_id, 'Action Y placeholder', FALSE),
      (kr_id, 'Action Z placeholder', FALSE);
  END LOOP;

END $$;
