-- =============================================================================
-- Marketplace Hunter: Construction Labour Marketplace Tables
-- Migration: 20260428000000_marketplace_hunter.sql
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1. aggregated_jobs — scraped job listings from Seek / Indeed / Gumtree
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS aggregated_jobs (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title            TEXT NOT NULL,
  company_name     TEXT,
  location         TEXT,
  suburb           TEXT,
  pay_rate         TEXT,
  trade_type       TEXT,
  description_snippet TEXT,
  source_url       TEXT UNIQUE NOT NULL,
  source           TEXT NOT NULL CHECK (source IN ('seek', 'indeed', 'gumtree')),
  date_posted      DATE,
  scraped_at       TIMESTAMPTZ DEFAULT NOW(),
  expired          BOOLEAN DEFAULT FALSE,
  created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_aggregated_jobs_trade_type ON aggregated_jobs (trade_type);
CREATE INDEX IF NOT EXISTS idx_aggregated_jobs_location   ON aggregated_jobs (location);
CREATE INDEX IF NOT EXISTS idx_aggregated_jobs_source     ON aggregated_jobs (source);

-- ---------------------------------------------------------------------------
-- 2. contact_registry — central dedup + anti-spam registry
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contact_registry (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email            TEXT,
  phone            TEXT,
  name             TEXT,
  city             TEXT,
  trade            TEXT,
  identity_key     TEXT GENERATED ALWAYS AS (
                     LOWER(
                       COALESCE(email, '') || '|' ||
                       COALESCE(phone, '') || '|' ||
                       COALESCE(name,  '') || '|' ||
                       COALESCE(city,  '')
                     )
                   ) STORED UNIQUE,
  side             TEXT CHECK (side IN ('worker', 'contractor', 'both')),
  source           TEXT CHECK (source IN ('gumtree', 'facebook', 'seek', 'indeed', 'manual', 'signup', 'match')),
  first_seen_at    TIMESTAMPTZ DEFAULT NOW(),
  last_contacted_at TIMESTAMPTZ,
  contact_count    INTEGER DEFAULT 0,
  max_contacts     INTEGER DEFAULT 3,
  status           TEXT DEFAULT 'new' CHECK (status IN ('new', 'contacted', 'replied', 'converted', 'unsubscribed', 'bounced')),
  unsubscribed_at  TIMESTAMPTZ,
  created_at       TIMESTAMPTZ DEFAULT NOW(),
  updated_at       TIMESTAMPTZ DEFAULT NOW()
);

-- Partial unique indexes to dedup on email or phone individually
CREATE UNIQUE INDEX IF NOT EXISTS idx_contact_registry_email
  ON contact_registry (email) WHERE email IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_contact_registry_phone
  ON contact_registry (phone) WHERE phone IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_contact_registry_city_trade ON contact_registry (city, trade);
CREATE INDEX IF NOT EXISTS idx_contact_registry_status     ON contact_registry (status);

-- ---------------------------------------------------------------------------
-- 3. scraped_leads — raw leads before outreach
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS scraped_leads (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source               TEXT NOT NULL CHECK (source IN ('gumtree', 'facebook', 'seek', 'indeed', 'manual')),
  source_url           TEXT,
  side                 TEXT NOT NULL CHECK (side IN ('worker', 'contractor')),
  name                 TEXT,
  email                TEXT,
  phone                TEXT,
  trade                TEXT,
  city                 TEXT,
  suburb               TEXT,
  description          TEXT,
  pay_rate             TEXT,
  post_date            DATE,
  status               TEXT DEFAULT 'new' CHECK (status IN ('new', 'queued', 'approved', 'sent', 'replied', 'converted', 'rejected', 'skipped')),
  contact_registry_id  UUID REFERENCES contact_registry (id),
  outreach_channel     TEXT CHECK (outreach_channel IN ('email', 'sms', 'facebook_dm', 'facebook_reply', 'gumtree_message')),
  outreach_sent_at     TIMESTAMPTZ,
  outreach_message_id  TEXT,
  scraped_at           TIMESTAMPTZ DEFAULT NOW(),
  created_at           TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (source, source_url)
);

-- Special dedup index for Facebook posts (no stable URL — dedup by name+city+date)
CREATE UNIQUE INDEX IF NOT EXISTS idx_scraped_leads_facebook_dedup
  ON scraped_leads (source, name, city, post_date)
  WHERE source = 'facebook';

CREATE INDEX IF NOT EXISTS idx_scraped_leads_side           ON scraped_leads (side);
CREATE INDEX IF NOT EXISTS idx_scraped_leads_city_trade     ON scraped_leads (city, trade);
CREATE INDEX IF NOT EXISTS idx_scraped_leads_status         ON scraped_leads (status);
CREATE INDEX IF NOT EXISTS idx_scraped_leads_source         ON scraped_leads (source);

-- ---------------------------------------------------------------------------
-- 4. match_introductions — worker ↔ contractor matches
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS match_introductions (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  worker_lead_id        UUID REFERENCES scraped_leads (id),
  contractor_lead_id    UUID REFERENCES scraped_leads (id),
  trade                 TEXT,
  city                  TEXT,
  score                 INTEGER,
  worker_contacted_at   TIMESTAMPTZ,
  contractor_contacted_at TIMESTAMPTZ,
  worker_signed_up      BOOLEAN DEFAULT FALSE,
  contractor_signed_up  BOOLEAN DEFAULT FALSE,
  hire_completed        BOOLEAN DEFAULT FALSE,
  created_at            TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (worker_lead_id, contractor_lead_id)
);

-- ---------------------------------------------------------------------------
-- 5. facebook_post_log — tracks Facebook group posts
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS facebook_post_log (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  group_name          TEXT NOT NULL,
  group_url           TEXT,
  post_type           TEXT CHECK (post_type IN ('question', 'tip', 'story', 'job_link')),
  post_content        TEXT,
  city                TEXT,
  posted_at           TIMESTAMPTZ DEFAULT NOW(),
  engagement_likes    INTEGER DEFAULT 0,
  engagement_comments INTEGER DEFAULT 0,
  removed_by_admin    BOOLEAN DEFAULT FALSE,
  notes               TEXT
);

CREATE INDEX IF NOT EXISTS idx_facebook_post_log_group_name ON facebook_post_log (group_name);
CREATE INDEX IF NOT EXISTS idx_facebook_post_log_posted_at  ON facebook_post_log (posted_at);
