-- Migration 065: Create company_teams table and add primary/billing contact FKs to companies

-- Company Teams: maps resources (TAMs, CSMs, etc.) to companies
CREATE TABLE IF NOT EXISTS company_teams (
  id                          BIGINT PRIMARY KEY,
  company_id                  BIGINT REFERENCES companies(id) ON DELETE CASCADE,
  resource_id                 BIGINT,
  is_associated_as_comanaged  BOOLEAN DEFAULT false,
  created_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at                  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  synced_at                   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_deleted                  BOOLEAN DEFAULT false,
  deleted_at                  TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_company_teams_company_id  ON company_teams(company_id);
CREATE INDEX IF NOT EXISTS idx_company_teams_resource_id ON company_teams(resource_id);

-- Add primary and billing contact FK columns to companies
ALTER TABLE companies
  ADD COLUMN IF NOT EXISTS primary_contact_id BIGINT REFERENCES contacts(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS billing_contact_id BIGINT REFERENCES contacts(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_companies_primary_contact ON companies(primary_contact_id) WHERE primary_contact_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_companies_billing_contact ON companies(billing_contact_id) WHERE billing_contact_id IS NOT NULL;

-- Backfill from existing contacts data
UPDATE companies c
SET primary_contact_id = (
  SELECT id FROM contacts
  WHERE company_id = c.id AND primary_contact = true AND is_deleted = false
  ORDER BY id LIMIT 1
)
WHERE primary_contact_id IS NULL;

UPDATE companies c
SET billing_contact_id = (
  SELECT id FROM contacts
  WHERE company_id = c.id AND billing_contact = true AND is_deleted = false
  ORDER BY id LIMIT 1
)
WHERE billing_contact_id IS NULL;