-- +goose Up
-- Contacts
CREATE TABLE IF NOT EXISTS contacts (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  first_name text NOT NULL DEFAULT '',
  last_name text NOT NULL DEFAULT '',
  email text NOT NULL DEFAULT '',
  phone text NOT NULL DEFAULT '',
  company_id text,
  title text NOT NULL DEFAULT '',
  notes text NOT NULL DEFAULT '',
  avatar_url text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS contacts_workspace_updated_idx ON contacts (workspace_slug, updated_at DESC);
CREATE INDEX IF NOT EXISTS contacts_workspace_email_idx ON contacts (workspace_slug, email);

-- Companies
CREATE TABLE IF NOT EXISTS companies (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  domain text NOT NULL DEFAULT '',
  website text NOT NULL DEFAULT '',
  industry text NOT NULL DEFAULT '',
  size text NOT NULL DEFAULT '',
  notes text NOT NULL DEFAULT '',
  logo_url text NOT NULL DEFAULT '',
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS companies_workspace_updated_idx ON companies (workspace_slug, updated_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS companies_workspace_name_idx ON companies (workspace_slug, name);

-- Add company foreign key to contacts
ALTER TABLE contacts ADD CONSTRAINT contacts_company_fk FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

-- Contact-Task links
CREATE TABLE IF NOT EXISTS contact_tasks (
  id text PRIMARY KEY,
  contact_id text NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  task_id text NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS contact_tasks_unique_idx ON contact_tasks (contact_id, task_id);

-- Contact-Event links
CREATE TABLE IF NOT EXISTS contact_events (
  id text PRIMARY KEY,
  contact_id text NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  event_id text NOT NULL REFERENCES calendar_events(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS contact_events_unique_idx ON contact_events (contact_id, event_id);

-- Contact-Email links (track which contacts are involved in emails)
CREATE TABLE IF NOT EXISTS contact_emails (
  id text PRIMARY KEY,
  contact_id text NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
  mail_message_id text NOT NULL REFERENCES mail_messages(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'recipient', -- sender, recipient, cc
  created_at timestamptz NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS contact_emails_unique_idx ON contact_emails (contact_id, mail_message_id);

-- +goose Down
DROP TABLE IF EXISTS contact_emails;
DROP TABLE IF EXISTS contact_events;
DROP TABLE IF EXISTS contact_tasks;
ALTER TABLE contacts DROP CONSTRAINT IF EXISTS contacts_company_fk;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS companies;