-- Backup / canonical storage: official printable DOCX+PDF roles + explicit storage_kind.
-- Apply: psql "$INITIATIVE_DATABASE_URL" -f migrations/009_backup_artifact_roles_storage_kind.sql

ALTER TABLE application_artifacts DROP CONSTRAINT IF EXISTS application_artifacts_role_check;
ALTER TABLE application_artifacts ADD CONSTRAINT application_artifacts_role_check CHECK (role IN (
  'full_pdf',
  'abstract',
  'poster',
  'textbook_evidence',
  'research_evidence',
  'technical_evidence',
  'other',
  'official_form_docx',
  'official_form_pdf'
));

ALTER TABLE application_artifacts
  ADD COLUMN IF NOT EXISTS storage_kind TEXT;

UPDATE application_artifacts SET storage_kind = CASE
  WHEN storage_uri LIKE 'http://%' OR storage_uri LIKE 'https://%' THEN 'external_url'
  WHEN storage_uri LIKE '/submitted-initiatives/%' THEN 'filesystem'
  WHEN role IN ('research_evidence', 'textbook_evidence', 'technical_evidence') THEN 'minio_attachments'
  ELSE 'minio_exports'
END
WHERE storage_kind IS NULL;

ALTER TABLE application_artifacts DROP CONSTRAINT IF EXISTS application_artifacts_storage_kind_check;
ALTER TABLE application_artifacts ADD CONSTRAINT application_artifacts_storage_kind_check
  CHECK (storage_kind IS NULL OR storage_kind IN (
    'minio_exports',
    'minio_attachments',
    'filesystem',
    'external_url'
  ));