-- Zoom Phone and Meetings Integration Tables
-- Supports cross-referencing with Autotask contacts/companies

CREATE TABLE IF NOT EXISTS zoom_users (
  id SERIAL PRIMARY KEY,
  zoom_id VARCHAR(255) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE,
  display_name VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  synced_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS zoom_calls (
  id SERIAL PRIMARY KEY,
  zoom_call_id VARCHAR(255) UNIQUE NOT NULL,
  resource_email VARCHAR(255),
  direction VARCHAR(20),
  call_status VARCHAR(20),
  other_party_number VARCHAR(50),
  other_party_name VARCHAR(255),
  start_time TIMESTAMPTZ,
  duration_seconds INTEGER,
  matched_contact_id BIGINT REFERENCES contacts(id),
  matched_company_id BIGINT REFERENCES companies(id),
  synced_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS zoom_meetings (
  id SERIAL PRIMARY KEY,
  zoom_meeting_id VARCHAR(255) UNIQUE NOT NULL,
  zoom_meeting_uuid VARCHAR(500),
  host_email VARCHAR(255),
  topic VARCHAR(500),
  start_time TIMESTAMPTZ,
  end_time TIMESTAMPTZ,
  duration_minutes INTEGER,
  participant_count INTEGER DEFAULT 0,
  client_participant_count INTEGER DEFAULT 0,
  has_client_attendees BOOLEAN DEFAULT false,
  synced_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS zoom_meeting_participants (
  id SERIAL PRIMARY KEY,
  meeting_id INTEGER NOT NULL REFERENCES zoom_meetings(id) ON DELETE CASCADE,
  participant_email VARCHAR(255),
  participant_name VARCHAR(255),
  duration_seconds INTEGER,
  matched_contact_id BIGINT REFERENCES contacts(id),
  matched_company_id BIGINT REFERENCES companies(id),
  is_internal BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_zoom_calls_resource_email_time ON zoom_calls(resource_email, start_time);
CREATE INDEX IF NOT EXISTS idx_zoom_calls_matched_company ON zoom_calls(matched_company_id);
CREATE INDEX IF NOT EXISTS idx_zoom_meetings_host_email_time ON zoom_meetings(host_email, start_time);
CREATE INDEX IF NOT EXISTS idx_zoom_meeting_participants_email ON zoom_meeting_participants(participant_email);