-- +goose Up
-- Recurring tasks
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS recurrence_rule text NOT NULL DEFAULT '';
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS recurrence_end timestamptz;

-- Recurring events
ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS recurrence_rule text NOT NULL DEFAULT '';
ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS recurrence_end timestamptz;

-- Quick capture inbox
CREATE TABLE IF NOT EXISTS inbox_items (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  content text NOT NULL,
  source text NOT NULL DEFAULT 'manual', -- manual, email, api
  processed boolean NOT NULL DEFAULT false,
  processed_at timestamptz,
  processed_entity_type text, -- task, note, event
  processed_entity_id text,
  created_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS inbox_items_workspace_created_idx ON inbox_items (workspace_slug, created_at DESC);

-- Time tracking
CREATE TABLE IF NOT EXISTS time_entries (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  task_id text REFERENCES tasks(id) ON DELETE CASCADE,
  description text NOT NULL DEFAULT '',
  started_at timestamptz NOT NULL,
  ended_at timestamptz,
  duration_seconds integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS time_entries_workspace_started_idx ON time_entries (workspace_slug, started_at DESC);
CREATE INDEX IF NOT EXISTS time_entries_task_idx ON time_entries (task_id);

-- Saved filters/views
CREATE TABLE IF NOT EXISTS saved_views (
  id text PRIMARY KEY,
  workspace_slug text NOT NULL REFERENCES workspaces(slug) ON DELETE CASCADE,
  name text NOT NULL,
  entity_type text NOT NULL, -- tasks, contacts, companies
  filter_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  sort_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  is_default boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL
);

CREATE INDEX IF NOT EXISTS saved_views_workspace_type_idx ON saved_views (workspace_slug, entity_type);

-- Archive support
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS archived boolean NOT NULL DEFAULT false;
ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS archived boolean NOT NULL DEFAULT false;
ALTER TABLE notes ADD COLUMN IF NOT EXISTS archived boolean NOT NULL DEFAULT false;

-- +goose Down
ALTER TABLE tasks DROP COLUMN IF EXISTS recurrence_rule;
ALTER TABLE tasks DROP COLUMN IF EXISTS recurrence_end;
ALTER TABLE calendar_events DROP COLUMN IF EXISTS recurrence_rule;
ALTER TABLE calendar_events DROP COLUMN IF EXISTS recurrence_end;
DROP TABLE IF EXISTS inbox_items;
DROP TABLE IF EXISTS time_entries;
DROP TABLE IF EXISTS saved_views;
ALTER TABLE tasks DROP COLUMN IF EXISTS archived;
ALTER TABLE calendar_events DROP COLUMN IF EXISTS archived;
ALTER TABLE notes DROP COLUMN IF EXISTS archived;