CREATE TABLE IF NOT EXISTS post_export_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  requested_by UUID REFERENCES users(id) ON DELETE SET NULL,
  requested_email TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL DEFAULT 'queued',
  scope TEXT NOT NULL DEFAULT 'all',
  post_count INTEGER NOT NULL DEFAULT 0,
  chunk_size INTEGER NOT NULL DEFAULT 100,
  retention_days INTEGER NOT NULL DEFAULT 100,
  expires_at TIMESTAMPTZ NOT NULL DEFAULT (now() + interval '100 days'),
  message TEXT NOT NULL DEFAULT '',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  CONSTRAINT post_export_jobs_status_check CHECK (status IN ('queued', 'processing', 'ready', 'failed', 'expired')),
  CONSTRAINT post_export_jobs_scope_check CHECK (scope IN ('all', 'author')),
  CONSTRAINT post_export_jobs_chunk_size_check CHECK (chunk_size > 0),
  CONSTRAINT post_export_jobs_retention_days_check CHECK (retention_days > 0 AND retention_days <= 100)
);

CREATE INDEX IF NOT EXISTS post_export_jobs_status_created_at_idx
  ON post_export_jobs (status, created_at DESC);

CREATE INDEX IF NOT EXISTS post_export_jobs_expires_at_idx
  ON post_export_jobs (expires_at);

CREATE TABLE IF NOT EXISTS post_export_files (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id UUID NOT NULL REFERENCES post_export_jobs(id) ON DELETE CASCADE,
  part_index INTEGER NOT NULL,
  post_start INTEGER NOT NULL,
  post_end INTEGER NOT NULL,
  file_name TEXT NOT NULL,
  file_path TEXT NOT NULL DEFAULT '',
  file_size_bytes BIGINT NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at TIMESTAMPTZ,
  CONSTRAINT post_export_files_status_check CHECK (status IN ('pending', 'processing', 'ready', 'failed', 'expired')),
  CONSTRAINT post_export_files_range_check CHECK (post_start > 0 AND post_end >= post_start),
  CONSTRAINT post_export_files_part_index_check CHECK (part_index > 0),
  CONSTRAINT post_export_files_job_part_unique UNIQUE (job_id, part_index)
);

CREATE INDEX IF NOT EXISTS post_export_files_job_id_part_index_idx
  ON post_export_files (job_id, part_index ASC);