-- +goose Up
CREATE TABLE IF NOT EXISTS workspaces (
  id text PRIMARY KEY,
  slug text NOT NULL UNIQUE,
  name text NOT NULL,
  role text NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS members (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  email text NOT NULL,
  role text NOT NULL,
  status text NOT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS members_workspace_email_idx ON members (workspace_slug, email);

CREATE TABLE IF NOT EXISTS invites (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  email text NOT NULL,
  role text NOT NULL,
  token text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL,
  status text NOT NULL
);

CREATE TABLE IF NOT EXISTS activity_entries (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  title text NOT NULL,
  detail text NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS activity_entries_workspace_created_idx ON activity_entries (workspace_slug, created_at DESC);

CREATE TABLE IF NOT EXISTS board_groups (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  color text NOT NULL,
  sort_order integer NOT NULL
);

CREATE TABLE IF NOT EXISTS labels (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  color text NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  board_group_id text NOT NULL REFERENCES board_groups(id) ON DELETE CASCADE,
  title text NOT NULL,
  description text NOT NULL DEFAULT '',
  status text NOT NULL,
  color text NOT NULL,
  due_at timestamptz,
  scheduled_start timestamptz,
  scheduled_end timestamptz,
  assignee_id text,
  label_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  attachments jsonb NOT NULL DEFAULT '[]'::jsonb,
  comments jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS tasks_workspace_updated_idx ON tasks (workspace_slug, updated_at DESC);

CREATE TABLE IF NOT EXISTS calendar_events (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  title text NOT NULL,
  description text NOT NULL DEFAULT '',
  starts_at timestamptz NOT NULL,
  ends_at timestamptz NOT NULL,
  color text NOT NULL,
  linked_task_id text,
  attachments jsonb NOT NULL DEFAULT '[]'::jsonb
);

CREATE INDEX IF NOT EXISTS calendar_events_workspace_starts_idx ON calendar_events (workspace_slug, starts_at ASC);

CREATE TABLE IF NOT EXISTS notes (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  title text NOT NULL,
  content text NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS focus_sessions (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  task_id text,
  mode text NOT NULL,
  started_at timestamptz NOT NULL,
  completed_at timestamptz,
  paused_at timestamptz,
  paused_total_seconds integer NOT NULL DEFAULT 0,
  duration_seconds integer NOT NULL
);

CREATE INDEX IF NOT EXISTS focus_sessions_workspace_started_idx ON focus_sessions (workspace_slug, started_at DESC);

-- +goose Down
DROP TABLE IF EXISTS focus_sessions;
DROP TABLE IF EXISTS notes;
DROP TABLE IF EXISTS calendar_events;
DROP TABLE IF EXISTS tasks;
DROP TABLE IF EXISTS labels;
DROP TABLE IF EXISTS board_groups;
DROP TABLE IF EXISTS activity_entries;
DROP TABLE IF EXISTS invites;
DROP INDEX IF EXISTS members_workspace_email_idx;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS workspaces;
