-- Migration 024: Add project archival tracking fields
-- Adds fields to track S3 archival of complete projects

ALTER TABLE projects
    ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP WITH TIME ZONE,
    ADD COLUMN IF NOT EXISTS archive_location TEXT,
    ADD COLUMN IF NOT EXISTS archive_size BIGINT,
    ADD COLUMN IF NOT EXISTS archived_by_session TEXT;

CREATE INDEX IF NOT EXISTS idx_projects_archived ON projects(archived_at) WHERE archived_at IS NOT NULL;

COMMENT ON COLUMN projects.archived_at IS 'Timestamp when project was archived to S3';
COMMENT ON COLUMN projects.archive_location IS 'S3 path to archived tarball (e.g., s3://agiliton-archive/projects/Project-20260127.tar.gz)';
COMMENT ON COLUMN projects.archive_size IS 'Size of archive in bytes';
COMMENT ON COLUMN projects.archived_by_session IS 'Session ID that performed the archival';

-- Record migration
INSERT INTO schema_migrations (version, applied_at)
VALUES ('024_add_project_archival_fields', NOW())
ON CONFLICT DO NOTHING;