-- Athena Typesense integration schema.
--
-- Apply this on the Athena logging database when enabling the optional
-- Typesense integration:
--
--   psql "$DATABASE_URL" -f sql/typesense.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS public.typesense_profiles (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL,
    base_url text NOT NULL,
    api_key text NOT NULL,
    search_api_key text,
    timeout_ms integer NOT NULL DEFAULT 10000,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_typesense_profiles_name
    ON public.typesense_profiles(name);

CREATE UNIQUE INDEX IF NOT EXISTS idx_typesense_profiles_base_url
    ON public.typesense_profiles(base_url);

CREATE INDEX IF NOT EXISTS idx_typesense_profiles_created_at
    ON public.typesense_profiles(created_at DESC);

CREATE TABLE IF NOT EXISTS public.typesense_sync_jobs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    typesense_sync_job_id uuid NOT NULL DEFAULT gen_random_uuid(),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    name text NOT NULL,
    enabled boolean NOT NULL DEFAULT true,
    auto_sync_enabled boolean NOT NULL DEFAULT false,
    sync_strategy text NOT NULL DEFAULT 'upsert'
        CHECK (sync_strategy IN ('upsert', 'full')),
    source_client_name text NOT NULL,
    source_schema_name text NOT NULL DEFAULT 'public',
    source_table_name text NOT NULL,
    typesense_profile_id bigint NOT NULL REFERENCES public.typesense_profiles(id) ON DELETE CASCADE,
    collection_name text NOT NULL,
    document_id_column text NOT NULL,
    selected_columns jsonb NOT NULL DEFAULT '[]'::jsonb,
    query_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    facet_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    sort_by_fields jsonb NOT NULL DEFAULT '[]'::jsonb,
    field_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    default_sort_field text,
    batch_size integer NOT NULL DEFAULT 250,
    sync_interval_minutes integer NOT NULL DEFAULT 60,
    stale_after_minutes integer NOT NULL DEFAULT 120,
    last_sync_status text NOT NULL DEFAULT 'idle'
        CHECK (last_sync_status IN ('idle', 'running', 'success', 'failed', 'paused')),
    last_sync_started_at timestamptz,
    last_sync_finished_at timestamptz,
    last_synced_at timestamptz,
    last_sync_row_count integer,
    last_sync_success_count integer,
    last_sync_error_count integer,
    last_sync_error text,
    next_sync_at timestamptz DEFAULT now(),
    sync_lock_token uuid,
    sync_locked_at timestamptz,
    sync_lock_expires_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_typesense_sync_jobs_name UNIQUE (name),
    CONSTRAINT uq_typesense_sync_jobs_binding
        UNIQUE (source_client_name, source_schema_name, source_table_name, typesense_profile_id, collection_name),
    CONSTRAINT uq_typesense_sync_jobs_uuid UNIQUE (typesense_sync_job_id)
);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_due
    ON public.typesense_sync_jobs(enabled, auto_sync_enabled, next_sync_at);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_source
    ON public.typesense_sync_jobs(source_client_name, source_schema_name, source_table_name);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_profile
    ON public.typesense_sync_jobs(typesense_profile_id, collection_name);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_jobs_last_synced
    ON public.typesense_sync_jobs(last_synced_at DESC);

CREATE TABLE IF NOT EXISTS public.typesense_sync_runs (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    typesense_sync_run_id uuid NOT NULL DEFAULT gen_random_uuid(),
    created_at timestamptz NOT NULL DEFAULT now(),
    job_id bigint NOT NULL REFERENCES public.typesense_sync_jobs(id) ON DELETE CASCADE,
    trigger_source text NOT NULL DEFAULT 'manual'
        CHECK (trigger_source IN ('manual', 'scheduled', 'api')),
    status text NOT NULL
        CHECK (status IN ('running', 'success', 'failed')),
    started_at timestamptz NOT NULL DEFAULT now(),
    finished_at timestamptz,
    source_row_count integer,
    estimated_total_rows integer,
    processed_row_count integer NOT NULL DEFAULT 0,
    imported_count integer,
    failed_count integer,
    deleted_count integer,
    duration_ms bigint,
    progress_pct integer NOT NULL DEFAULT 0
        CHECK (progress_pct >= 0 AND progress_pct <= 100),
    progress_stage text NOT NULL DEFAULT 'queued',
    progress_message text,
    batches_completed integer NOT NULL DEFAULT 0,
    error_message text,
    result_preview jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT uq_typesense_sync_runs_uuid UNIQUE (typesense_sync_run_id)
);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_runs_job_started_at
    ON public.typesense_sync_runs(job_id, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_typesense_sync_runs_status
    ON public.typesense_sync_runs(status, started_at DESC);

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;
