-- Migration 031: Archive task tables after Jira Cloud migration (CF-762)
-- Task management moved to Jira Cloud. Archive local task tables for historical reference.
-- Session, memory, archive, and infrastructure tables remain active.

BEGIN;

-- 1. Archive task tables (rename with archived_ prefix)
ALTER TABLE IF EXISTS tasks RENAME TO archived_tasks;
ALTER TABLE IF EXISTS task_checklist RENAME TO archived_task_checklist;
ALTER TABLE IF EXISTS task_links RENAME TO archived_task_links;
ALTER TABLE IF EXISTS task_activity RENAME TO archived_task_activity;
ALTER TABLE IF EXISTS task_sequences RENAME TO archived_task_sequences;

-- 2. Add archived_at timestamp to archived tables
ALTER TABLE IF EXISTS archived_tasks ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE IF EXISTS archived_task_checklist ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE IF EXISTS archived_task_links ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE IF EXISTS archived_task_activity ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE IF EXISTS archived_task_sequences ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- 3. Drop tables that are fully replaced by Jira (data already migrated)
DROP TABLE IF EXISTS epics CASCADE;
DROP TABLE IF EXISTS epic_sequences CASCADE;
DROP TABLE IF EXISTS versions CASCADE;

-- 4. Keep these tables (still referenced by session tools):
--    - task_commits (git commit ↔ Jira issue linking)
--    - task_migration_map (maps old local IDs → Jira keys)
--    - task_delegations (code delegation tracking)

-- 5. Update task_commits to remove FK constraint on archived_tasks
-- (commits now reference Jira issue keys, not local task IDs)
ALTER TABLE IF EXISTS task_commits DROP CONSTRAINT IF EXISTS task_commits_task_id_fkey;

-- 6. Update task_delegations to remove FK constraint on archived_tasks
ALTER TABLE IF EXISTS task_delegations DROP CONSTRAINT IF EXISTS task_delegations_task_id_fkey;

-- 7. Drop unused indexes on archived tables (save space, they're read-only now)
DROP INDEX IF EXISTS idx_tasks_status;
DROP INDEX IF EXISTS idx_tasks_type;
DROP INDEX IF EXISTS idx_tasks_priority;
DROP INDEX IF EXISTS idx_tasks_epic;
DROP INDEX IF EXISTS idx_tasks_version;
DROP INDEX IF EXISTS idx_tasks_embedding;

-- 8. Record migration
INSERT INTO schema_migrations (version, applied_at) VALUES ('031_archive_task_tables', NOW())
ON CONFLICT DO NOTHING;

COMMIT;