Platform Architecture Plan

-- Migration 003: pipeline_opportunities
CREATE TABLE pipeline_opportunities (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug            TEXT UNIQUE NOT NULL,          -- matches documents/opportunities/[slug]/
  title           TEXT NOT NULL,
  subtitle        TEXT,
  client_name     TEXT,
  client_company  TEXT,
  stage           TEXT NOT NULL DEFAULT 'identified'
                  CHECK (stage IN ('identified','briefing_sent','in_dialogue',
                                   'proposal_sent','won','lost')),
  value_estimate  INTEGER,                       -- AUD rough estimate
  probability     INTEGER CHECK (probability BETWEEN 0 AND 100),
  briefing_sent_at TIMESTAMPTZ,
  next_action     TEXT,
  next_action_due DATE,
  notes           TEXT,
  tags            TEXT[] DEFAULT '{}',
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE pipeline_opportunities ENABLE ROW LEVEL SECURITY;
CREATE POLICY "pipeline_authenticated" ON pipeline_opportunities
  FOR ALL TO authenticated USING (true) WITH CHECK (true);

-- Migration 004: contacts, companies, activities
CREATE TABLE companies (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name       TEXT NOT NULL,
  domain     TEXT UNIQUE,
  industry   TEXT,
  website    TEXT,
  notes      TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE contacts (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  display_name    TEXT NOT NULL,
  primary_email   TEXT UNIQUE,
  phone           TEXT,
  title           TEXT,
  company_id      UUID REFERENCES companies(id) ON DELETE SET NULL,
  warmth          TEXT DEFAULT 'cold'
                  CHECK (warmth IN ('cold','warm','hot','customer','dormant')),
  next_action     TEXT,
  next_action_due DATE,
  notes           TEXT,
  tags            TEXT[] DEFAULT '{}',
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE activities (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  contact_id      UUID REFERENCES contacts(id) ON DELETE CASCADE,
  opportunity_id  UUID REFERENCES pipeline_opportunities(id) ON DELETE SET NULL,
  activity_type   TEXT CHECK (activity_type IN
                    ('call','email','meeting','note','proposal')),
  summary         TEXT NOT NULL,
  occurred_at     TIMESTAMPTZ DEFAULT now(),
  created_by      TEXT
);

-- Link contacts to opportunities (added after Phase 3)
ALTER TABLE pipeline_opportunities
  ADD COLUMN contact_id UUID REFERENCES contacts(id) ON DELETE SET NULL;