-- Migration 059: Create project_phases table
-- Project phases are groupings of tasks within an Autotask project.
-- Tasks reference phases via phase_id. Syncing phases allows us to resolve
-- tasks back to their parent project (tasks.phase_id → project_phases.id → project_phases.project_id).
-- Autotask endpoint: /Phases

CREATE TABLE IF NOT EXISTS project_phases (
  id                      BIGINT PRIMARY KEY,
  project_id              BIGINT, -- no FK: phases may reference completed/archived projects not in our DB
  parent_phase_id         BIGINT,
  title                   CHARACTER VARYING,
  description             TEXT,
  phase_number            CHARACTER VARYING,
  estimated_hours         NUMERIC,
  start_date_time         TIMESTAMP WITHOUT TIME ZONE,
  due_date                TIMESTAMP WITHOUT TIME ZONE,
  create_date_time        TIMESTAMP WITHOUT TIME ZONE,
  last_activity_date_time TIMESTAMP WITHOUT TIME ZONE,
  creator_resource_id     BIGINT,
  is_scheduled            BOOLEAN DEFAULT FALSE,
  external_id             CHARACTER VARYING,
  synced_at               TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at              TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at              TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_deleted              BOOLEAN DEFAULT FALSE,
  deleted_at              TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_project_phases_project_id ON project_phases (project_id);
CREATE INDEX IF NOT EXISTS idx_project_phases_is_deleted ON project_phases (is_deleted) WHERE is_deleted = FALSE;