-- Migration: Add company_contacts table for admin/additional contacts
-- Run via Supabase Management API

CREATE TABLE IF NOT EXISTS company_contacts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id uuid NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name text NOT NULL,
  role text,  -- e.g. 'Site Supervisor', 'Admin', 'Accounts'
  phone text,
  email text,
  is_primary boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Index for quick lookup by company
CREATE INDEX idx_company_contacts_company_id ON company_contacts(company_id);

-- Trigger for updated_at
CREATE TRIGGER company_contacts_updated_at
  BEFORE UPDATE ON company_contacts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- RLS policies
ALTER TABLE company_contacts ENABLE ROW LEVEL SECURITY;

-- Contractors can manage their own company contacts
CREATE POLICY "company_contacts_select" ON company_contacts
  FOR SELECT USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "company_contacts_insert" ON company_contacts
  FOR INSERT WITH CHECK (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "company_contacts_update" ON company_contacts
  FOR UPDATE USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

CREATE POLICY "company_contacts_delete" ON company_contacts
  FOR DELETE USING (
    company_id IN (
      SELECT id FROM companies WHERE profile_id = auth.uid()
    )
  );

-- Workers can see contacts for companies they're matched with
CREATE POLICY "company_contacts_worker_view" ON company_contacts
  FOR SELECT USING (
    company_id IN (
      SELECT c.id FROM companies c
      JOIN jobs j ON j.company_id = c.id
      JOIN matches m ON m.job_id = j.id
      WHERE m.worker_id = auth.uid()
      AND m.status IN ('accepted', 'hired')
    )
  );
