43 lines
1.8 KiB
MySQL
43 lines
1.8 KiB
MySQL
|
-- 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;
|