Query 1 - Form Submissions:
  is:unread subject:(demo OR trial OR "get started" OR "sign up" OR "contact us" OR "request info")

Query 2 - Partnership Inquiries:
  is:unread subject:(partnership OR collaborate OR integration OR "work together" OR referral)

Query 3 - Direct Outreach / RFPs:
  is:unread subject:(proposal OR RFP OR consulting OR engagement OR "looking for" OR "need help")

Query 4 - Warm Referrals:
  is:unread subject:(introduction OR intro OR referral OR "meet" OR "connecting you")

Query 5 - Pricing/Service Inquiries:
  is:unread subject:(pricing OR rates OR "how much" OR services OR scope OR availability)

{
  "contact": {
    "name": "string -- full name of the person reaching out",
    "email": "string -- sender email address",
    "phone": "string|null -- phone number if mentioned in email or signature",
    "role": "string|null -- job title or role if mentioned",
    "linkedin": "string|null -- LinkedIn URL if included in signature"
  },
  "company": {
    "name": "string|null -- company name from domain, signature, or email body",
    "domain": "string -- extracted from email domain",
    "size_estimate": "string|null -- startup/smb/midmarket/enterprise based on clues",
    "industry": "string|null -- industry if determinable from context"
  },
  "inquiry": {
    "type": "string -- one of: demo_request, partnership, rfp, referral, pricing_inquiry, general_inquiry, support_question",
    "summary": "string -- 1-2 sentence summary of what they need",
    "specific_services": ["string -- list of specific services or capabilities mentioned"],
    "pain_points": ["string -- specific problems or challenges mentioned"],
    "timeline": "string|null -- any timeline or deadline mentioned",
    "budget_signals": "string|null -- any budget or pricing context mentioned"
  },
  "metadata": {
    "source": "string -- gmail",
    "source_detail": "string -- which search query matched",
    "message_id": "string",
    "thread_id": "string",
    "received_date": "string -- ISO 8601",
    "forwarded_by": "string|null -- if this was a forwarded intro",
    "cc_stakeholders": ["string -- CC'd email addresses that may be stakeholders"]
  }
}

Company Size Alignment:
  Enterprise (1000+ employees)      = 15 points
  Mid-market (100-999 employees)    = 12 points
  SMB (10-99 employees)             = 8 points
  Startup/Micro (1-9 employees)     = 4 points
  Unknown                           = 5 points (neutral)

Industry Alignment (configure per firm):
  Primary target industries         = 15 points
  Secondary/adjacent industries     = 10 points
  Neutral industries                = 5 points
  Misaligned industries             = 2 points
  Unknown                           = 5 points (neutral)

Role/Authority:
  C-suite, VP, Director (decision maker)        = 10 points
  Manager, Head of (influencer)                 = 7 points
  Individual contributor                        = 4 points
  Unknown role                                  = 3 points

Inquiry Type:
  RFP / Formal proposal request     = 12 points
  Demo / Trial request               = 10 points
  Pricing inquiry                    = 9 points
  Referral / Introduction            = 8 points
  Partnership inquiry                = 6 points
  General inquiry                    = 4 points
  Support question (not a lead)      = 0 points

Language Signals (cumulative, max 15 points):
  Mentions specific services by name          = +4
  Describes a concrete problem/pain point     = +4
  Asks about process, timeline, or next steps = +3
  References competitor or current vendor     = +3
  Mentions team/stakeholder involvement       = +2
  Uses vague/exploratory language only        = +1

Engagement Depth:
  Long, detailed email (200+ words)    = 8 points
  Medium email (50-200 words)          = 5 points
  Short email (under 50 words)         = 2 points

Explicit Timeline:
  Deadline within 2 weeks            = 12 points
  Deadline within 1 month            = 9 points
  Deadline within 1 quarter          = 6 points
  Vague future timeline              = 3 points
  No timeline mentioned              = 1 point

Urgency Language (cumulative, max 8 points):
  "ASAP" / "urgent" / "immediately"          = +4
  "This week" / "this month"                 = +3
  "Soon" / "in the near future"              = +2
  "Exploring" / "researching"                = +1

Contextual Urgency (max 5 points):
  Regulatory deadline mentioned       = +5
  Board/executive mandate             = +4
  Competitive pressure                = +3
  Budget expiring                     = +5
  Seasonal/event driven               = +3

Total Score Range: 0-100 points

HOT LEAD (75-100):
  - Qualification: Sales-Qualified Lead (SQL)
  - Action: Respond within 2 hours, book a call
  - Priority: P1 -- immediate attention
  - Draft: Eager, specific, offer calendar link

WARM LEAD (50-74):
  - Qualification: Marketing-Qualified Lead (MQL)
  - Action: Respond within 24 hours, ask qualifying questions
  - Priority: P2 -- same-day response
  - Draft: Warm, curious, ask 2-3 discovery questions

COOL LEAD (25-49):
  - Qualification: Information-Qualified Lead (IQL)
  - Action: Respond within 48 hours, nurture
  - Priority: P3 -- respond when time allows
  - Draft: Helpful, educational, offer resources

COLD / NOT A LEAD (0-24):
  - Qualification: Unqualified
  - Action: Polite response or no action
  - Priority: P4 -- low priority
  - Draft: Brief acknowledgment, redirect if needed

Objective: Get a call booked within 48 hours
Tone: Enthusiastic but professional, specific and knowledgeable
Length: 150-200 words
Structure:
  1. Acknowledge their specific need (reference their exact words)
  2. Brief credibility statement (1 sentence, relevant to their ask)
  3. Confirm you can help with [specific thing they mentioned]
  4. Propose 2-3 specific meeting times this week
  5. Warm sign-off

Subject: Re: [Original Subject]

Hi [First Name],

Thanks for reaching out about [specific need they mentioned]. This is exactly
the kind of [project/challenge/initiative] we specialize in.

[One sentence of relevant credibility -- e.g., "We recently helped a similar
[industry] company [achieve specific result]."]

I'd love to learn more about [specific detail from their email] and discuss
how we might be able to help. Would any of these work for a quick intro call?

  - [Day], [Time] [Timezone]
  - [Day], [Time] [Timezone]
  - [Day], [Time] [Timezone]

Or feel free to grab a time that works for you: [calendar link placeholder]

Looking forward to connecting.

Best,
[User's name]

Objective: Qualify further with 2-3 discovery questions
Tone: Curious, helpful, genuinely interested
Length: 120-180 words
Structure:
  1. Thank them for reaching out (reference what they mentioned)
  2. Show understanding of their situation
  3. Ask 2-3 specific qualifying questions
  4. Offer to set up a call once you understand more
  5. Warm sign-off

Objective: Provide value, stay top of mind, nurture
Tone: Helpful, educational, no pressure
Length: 100-150 words
Structure:
  1. Thank them for their interest
  2. Provide a helpful insight related to their inquiry
  3. Share a relevant resource (case study, blog post, guide)
  4. Leave the door open for further conversation
  5. Warm sign-off

CREATE TABLE IF NOT EXISTS leads (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),

  -- Contact info
  contact_name TEXT NOT NULL,
  contact_email TEXT NOT NULL,
  contact_phone TEXT,
  contact_role TEXT,
  contact_linkedin TEXT,

  -- Company info
  company_name TEXT,
  company_domain TEXT,
  company_size TEXT CHECK (company_size IN ('startup', 'smb', 'midmarket', 'enterprise', 'unknown')),
  company_industry TEXT,

  -- Inquiry details
  inquiry_type TEXT NOT NULL CHECK (inquiry_type IN (
    'demo_request', 'partnership', 'rfp', 'referral',
    'pricing_inquiry', 'general_inquiry', 'support_question'
  )),
  inquiry_summary TEXT NOT NULL,
  specific_services TEXT[],
  pain_points TEXT[],
  timeline TEXT,
  budget_signals TEXT,

  -- Scoring
  score_total INTEGER NOT NULL DEFAULT 0,
  score_icp INTEGER NOT NULL DEFAULT 0,
  score_intent INTEGER NOT NULL DEFAULT 0,
  score_urgency INTEGER NOT NULL DEFAULT 0,
  qualification TEXT NOT NULL CHECK (qualification IN ('sql', 'mql', 'iql', 'unqualified')),
  priority TEXT NOT NULL CHECK (priority IN ('P1', 'P2', 'P3', 'P4')),
  score_adjustments JSONB DEFAULT '[]'::jsonb,

  -- Source tracking
  source TEXT NOT NULL DEFAULT 'gmail',
  source_detail TEXT,
  gmail_message_id TEXT UNIQUE,
  gmail_thread_id TEXT,
  received_date TIMESTAMPTZ,
  forwarded_by TEXT,

  -- Response tracking
  response_drafted BOOLEAN DEFAULT false,
  response_draft_id TEXT,
  response_draft_text TEXT,
  response_sent BOOLEAN DEFAULT false,
  response_sent_date TIMESTAMPTZ,

  -- Pipeline tracking
  stage TEXT NOT NULL DEFAULT 'new' CHECK (stage IN (
    'new', 'contacted', 'qualifying', 'proposal', 'negotiation', 'won', 'lost', 'nurture'
  )),
  next_action TEXT,
  next_action_date TIMESTAMPTZ,
  assigned_to TEXT,
  notes TEXT,
  tags TEXT[]
);

-- Indexes for common queries
CREATE INDEX IF NOT EXISTS idx_leads_email ON leads(contact_email);
CREATE INDEX IF NOT EXISTS idx_leads_company ON leads(company_domain);
CREATE INDEX IF NOT EXISTS idx_leads_score ON leads(score_total DESC);
CREATE INDEX IF NOT EXISTS idx_leads_stage ON leads(stage);
CREATE INDEX IF NOT EXISTS idx_leads_created ON leads(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_leads_qualification ON leads(qualification);
CREATE INDEX IF NOT EXISTS idx_leads_gmail_msg ON leads(gmail_message_id);

CREATE TABLE IF NOT EXISTS lead_activity_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id UUID NOT NULL REFERENCES leads(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  activity_type TEXT NOT NULL CHECK (activity_type IN (
    'email_received', 'email_drafted', 'email_sent',
    'score_updated', 'stage_changed', 'note_added',
    'call_scheduled', 'call_completed', 'meeting_booked',
    'proposal_sent', 'follow_up_scheduled'
  )),
  description TEXT NOT NULL,
  metadata JSONB DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_activity_lead ON lead_activity_log(lead_id);
CREATE INDEX IF NOT EXISTS idx_activity_type ON lead_activity_log(activity_type);
CREATE INDEX IF NOT EXISTS idx_activity_created ON lead_activity_log(created_at DESC);

CREATE TABLE IF NOT EXISTS pipeline_config (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  config_key TEXT UNIQUE NOT NULL,
  config_value JSONB NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Default ICP configuration
INSERT INTO pipeline_config (config_key, config_value) VALUES
  ('icp_primary_industries', '["Technology", "SaaS", "Financial Services", "Healthcare Tech", "Professional Services"]'::jsonb),
  ('icp_secondary_industries', '["E-commerce", "Manufacturing", "Education", "Real Estate Tech", "Media"]'::jsonb),
  ('icp_target_company_sizes', '["midmarket", "enterprise"]'::jsonb),
  ('icp_target_roles', '["C-suite", "VP", "Director", "Head of"]'::jsonb),
  ('response_calendar_link', '"https://calendly.com/PLACEHOLDER"'::jsonb),
  ('response_sender_name', '"PLACEHOLDER"'::jsonb),
  ('daily_report_recipients', '[]'::jsonb)
ON CONFLICT (config_key) DO NOTHING;

SELECT id, score_total, stage FROM leads
WHERE gmail_message_id = '[message_id]'
   OR (contact_email = '[email]' AND company_domain = '[domain]' AND created_at > now() - interval '30 days')
LIMIT 1;

INSERT INTO leads (
  contact_name, contact_email, contact_phone, contact_role, contact_linkedin,
  company_name, company_domain, company_size, company_industry,
  inquiry_type, inquiry_summary, specific_services, pain_points, timeline, budget_signals,
  score_total, score_icp, score_intent, score_urgency, qualification, priority,
  score_adjustments,
  source, source_detail, gmail_message_id, gmail_thread_id, received_date, forwarded_by,
  response_drafted, response_draft_text, next_action, next_action_date
) VALUES (
  -- [extracted values from lead profile]
);

INSERT INTO lead_activity_log (lead_id, activity_type, description, metadata)
VALUES (
  '[new_lead_id]',
  'email_received',
  'Inbound [inquiry_type] from [contact_name] at [company_name]',
  '{"subject": "[subject]", "score": [score], "qualification": "[qual]"}'::jsonb
);

INSERT INTO lead_activity_log (lead_id, activity_type, description, metadata)
VALUES (
  '[new_lead_id]',
  'email_drafted',
  'Auto-drafted [priority] response for review',
  '{"draft_id": "[gmail_draft_id]", "template_tier": "[hot/warm/cool]"}'::jsonb
);

P1 (HOT):  next_action = "Review and send draft response"
           next_action_date = now()

P2 (WARM): next_action = "Review draft, personalize, and send"
           next_action_date = now() + interval '4 hours'

P3 (COOL): next_action = "Review and send nurture response"
           next_action_date = now() + interval '24 hours'

P4 (COLD): next_action = "Review -- may not require response"
           next_action_date = now() + interval '48 hours'

undefined

undefined

-- Pipeline snapshot by stage
SELECT stage, COUNT(*) as count, AVG(score_total) as avg_score,
       MIN(created_at) as oldest
FROM leads
WHERE stage NOT IN ('won', 'lost')
GROUP BY stage;

-- Leads at risk (no recent activity)
SELECT l.contact_name, l.company_name, l.stage,
       MAX(a.created_at) as last_activity
FROM leads l
LEFT JOIN lead_activity_log a ON a.lead_id = l.id
WHERE l.stage NOT IN ('won', 'lost', 'nurture')
GROUP BY l.id, l.contact_name, l.company_name, l.stage
HAVING MAX(a.created_at) < now() - interval '7 days'
   OR MAX(a.created_at) IS NULL;

-- Score distribution
SELECT
  CASE
    WHEN score_total >= 90 THEN '90-100'
    WHEN score_total >= 75 THEN '75-89'
    WHEN score_total >= 50 THEN '50-74'
    WHEN score_total >= 25 THEN '25-49'
    ELSE '0-24'
  END as range,
  COUNT(*) as count
FROM leads
WHERE created_at > now() - interval '30 days'
GROUP BY range
ORDER BY range DESC;

1. CONNECT
   |-- Verify Gmail MCP Connector is available
   |-- Verify Supabase MCP Connector is available
   |-- Check if CRM tables exist (create if first run)
   |-- Load ICP config from pipeline_config table
   |
2. SEARCH
   |-- Execute all 5 Gmail search queries
   |-- Collect unique message IDs
   |-- Report: "Found [N] potential lead emails"
   |
3. PROCESS (for each email)
   |-- Read full message content
   |-- Extract lead profile
   |-- Score on ICP + Intent + Urgency
   |-- Apply score adjustments
   |-- Determine qualification tier
   |-- Report: "[Name] from [Company] -- Score: [X] ([Tier])"
   |
4. RESPOND (for qualified leads, score >= 25)
   |-- Select response template based on tier
   |-- Draft personalized response
   |-- Create draft in Gmail (DO NOT SEND)
   |-- Report: "Draft created for [Name]"
   |
5. LOG
   |-- Check for duplicates in Supabase
   |-- Insert/update lead record
   |-- Log all activities
   |-- Set next actions
   |-- Report: "Logged [N] leads to CRM"
   |
6. REPORT
   |-- Query pipeline snapshot from Supabase
   |-- Generate lead-pipeline-report.md
   |-- Display executive summary to user
   |-- Report: "Pipeline report saved"