ALTER TABLE public.typesense_sync_runs
    ADD COLUMN IF NOT EXISTS estimated_total_rows integer,
    ADD COLUMN IF NOT EXISTS processed_row_count integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS progress_pct integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS progress_stage text NOT NULL DEFAULT 'queued',
    ADD COLUMN IF NOT EXISTS progress_message text,
    ADD COLUMN IF NOT EXISTS batches_completed integer NOT NULL DEFAULT 0;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'typesense_sync_runs_progress_pct_check'
    ) THEN
        ALTER TABLE public.typesense_sync_runs
            ADD CONSTRAINT typesense_sync_runs_progress_pct_check
            CHECK (progress_pct >= 0 AND progress_pct <= 100);
    END IF;
END $$;

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.estimated_total_rows AS active_estimated_total_rows,
        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.batches_completed AS active_batches_completed
    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.sync_interval_minutes,
    j.stale_after_minutes,
    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_estimated_total_rows,
    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_batches_completed,
    CASE
        WHEN NOT j.enabled THEN false
        WHEN j.last_sync_status = 'failed' 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_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;
