-- +goose Up
-- Integrations table for external service connections
CREATE TABLE IF NOT EXISTS integrations (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  provider text NOT NULL, -- google_calendar, slack, etc.
  name text NOT NULL,
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  credentials_ciphertext text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  last_sync_at timestamptz,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS integrations_workspace_provider_idx ON integrations (workspace_slug, provider);

-- Webhooks for external notifications
CREATE TABLE IF NOT EXISTS webhooks (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  url text NOT NULL,
  secret text NOT NULL,
  events jsonb NOT NULL DEFAULT '[]'::jsonb, -- ["task.created", "task.completed", etc.]
  active boolean NOT NULL DEFAULT true,
  last_triggered_at timestamptz,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS webhooks_workspace_idx ON webhooks (workspace_slug);

-- Notifications for users
CREATE TABLE IF NOT EXISTS notifications (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  user_email text NOT NULL,
  type text NOT NULL, -- task_assigned, mention, comment, etc.
  title text NOT NULL,
  body text NOT NULL DEFAULT '',
  entity_type text, -- task, event, note
  entity_id text,
  read boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS notifications_user_created_idx ON notifications (user_email, created_at DESC);
CREATE INDEX IF NOT EXISTS notifications_unread_idx ON notifications (user_email, read) WHERE read = false;

-- Presence tracking for real-time collaboration
CREATE TABLE IF NOT EXISTS presence (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  user_email text NOT NULL,
  user_name text NOT NULL,
  entity_type text, -- board, task, note, etc.
  entity_id text,
  last_seen_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS presence_workspace_entity_idx ON presence (workspace_slug, entity_type, entity_id);
CREATE INDEX IF NOT EXISTS presence_user_idx ON presence (user_email);

-- +goose Down
DROP TABLE IF EXISTS presence;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS webhooks;
DROP TABLE IF EXISTS integrations;