ALTER TABLE public.typesense_sync_jobs
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_execution_daemon
    ON public.typesense_sync_jobs (execution_daemon_id);

ALTER TABLE public.backup_schedules
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

CREATE INDEX IF NOT EXISTS idx_backup_schedules_execution_daemon
    ON public.backup_schedules (execution_daemon_id);

ALTER TABLE public.backup_jobs
    ADD COLUMN IF NOT EXISTS execution_daemon_id text;

CREATE INDEX IF NOT EXISTS idx_backup_jobs_execution_daemon
    ON public.backup_jobs (execution_daemon_id);

CREATE OR REPLACE VIEW public.typesense_sync_job_status AS
WITH latest_running_run AS (
    SELECT DISTINCT ON (r.job_id)
        r.job_id,
        r.id AS active_run_id,
        r.status AS active_run_status,
        r.started_at AS active_run_started_at,
        r.progress_total_kind AS active_progress_total_kind,
        r.estimated_total_rows AS active_estimated_total_rows,
        r.manifest_total_rows AS active_manifest_total_rows,
        r.manifest_processed_rows AS active_manifest_processed_rows,
        r.next_manifest_seq AS active_next_manifest_seq,
        r.resume_attempt_count AS active_resume_attempt_count,
        r.processed_row_count AS active_processed_row_count,
        r.imported_count AS active_imported_count,
        r.failed_count AS active_failed_count,
        r.progress_pct AS active_progress_pct,
        r.progress_stage AS active_progress_stage,
        r.progress_message AS active_progress_message,
        r.inter_batch_delay_ms AS active_inter_batch_delay_ms,
        r.next_batch_at AS active_next_batch_at,
        r.batches_completed AS active_batches_completed,
        r.retry_attempt_count AS active_retry_attempt_count,
        r.rollback_attempted AS active_rollback_attempted,
        r.rollback_status AS active_rollback_status,
        r.rollback_restored_count AS active_rollback_restored_count,
        r.rollback_deleted_count AS active_rollback_deleted_count,
        r.rollback_error_message AS active_rollback_error_message
    FROM public.typesense_sync_runs r
    WHERE r.status = 'running'
    ORDER BY r.job_id, r.started_at DESC, r.id DESC
)
SELECT
    j.id,
    j.typesense_sync_job_id,
    j.created_at,
    j.updated_at,
    j.name,
    j.enabled,
    j.auto_sync_enabled,
    j.sync_strategy,
    j.source_client_name,
    j.source_schema_name,
    j.source_table_name,
    j.typesense_profile_id,
    p.name AS profile_name,
    p.base_url AS profile_base_url,
    j.collection_name,
    j.document_id_column,
    j.selected_columns,
    j.query_by_fields,
    j.facet_by_fields,
    j.sort_by_fields,
    j.default_sort_field,
    j.batch_size,
    j.batch_delay_ms,
    j.sync_interval_minutes,
    j.stale_after_minutes,
    j.execution_daemon_id,
    j.last_sync_status,
    j.last_sync_started_at,
    j.last_sync_finished_at,
    j.last_synced_at,
    j.last_sync_row_count,
    j.last_sync_success_count,
    j.last_sync_error_count,
    j.last_sync_error,
    j.next_sync_at,
    j.sync_locked_at,
    j.sync_lock_expires_at,
    rr.active_run_id,
    rr.active_run_status,
    rr.active_run_started_at,
    rr.active_progress_total_kind,
    rr.active_estimated_total_rows,
    rr.active_manifest_total_rows,
    rr.active_manifest_processed_rows,
    rr.active_next_manifest_seq,
    rr.active_resume_attempt_count,
    rr.active_processed_row_count,
    rr.active_imported_count,
    rr.active_failed_count,
    rr.active_progress_pct,
    rr.active_progress_stage,
    rr.active_progress_message,
    rr.active_inter_batch_delay_ms,
    rr.active_next_batch_at,
    rr.active_batches_completed,
    rr.active_retry_attempt_count,
    rr.active_rollback_attempted,
    rr.active_rollback_status,
    rr.active_rollback_restored_count,
    rr.active_rollback_deleted_count,
    rr.active_rollback_error_message,
    CASE
        WHEN NOT j.enabled THEN false
        WHEN j.last_sync_status IN ('failed', 'cancelled') THEN true
        WHEN j.last_synced_at IS NULL THEN true
        ELSE j.last_synced_at < now() - make_interval(mins => GREATEST(j.stale_after_minutes, 1)::int)
    END AS is_stale,
    CASE
        WHEN NOT j.enabled THEN 'disabled'
        WHEN j.last_sync_status = 'failed' THEN 'failed'
        WHEN j.last_sync_status = 'cancelled' THEN 'cancelled'
        WHEN j.last_synced_at IS NULL THEN 'never_synced'
        WHEN j.last_synced_at < now() - make_interval(mins => GREATEST(j.stale_after_minutes, 1)::int) THEN 'overdue'
        ELSE 'fresh'
    END AS sync_health
FROM public.typesense_sync_jobs j
JOIN public.typesense_profiles p
    ON p.id = j.typesense_profile_id
LEFT JOIN latest_running_run rr
    ON rr.job_id = j.id;
