--
-- Records queries
-- All SQL for api/routes/records.js
--

SET search_path TO dataflow, public;

-- ── Read ──────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION list_records(
    p_source_name     TEXT,
    p_limit           INT     DEFAULT 100,
    p_offset          INT     DEFAULT 0,
    p_transformed_only BOOLEAN DEFAULT FALSE
)
RETURNS SETOF dataflow.records AS $$
    SELECT * FROM dataflow.records
    WHERE source_name = p_source_name
      AND (NOT p_transformed_only OR transformed IS NOT NULL)
    ORDER BY id DESC
    LIMIT p_limit OFFSET p_offset;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION get_record(p_id BIGINT)
RETURNS dataflow.records AS $$
    SELECT * FROM dataflow.records WHERE id = p_id;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION search_records(
    p_source_name TEXT,
    p_query       JSONB,
    p_limit       INT DEFAULT 100
)
RETURNS SETOF dataflow.records AS $$
    SELECT * FROM dataflow.records
    WHERE source_name = p_source_name
      AND (data @> p_query OR transformed @> p_query)
    ORDER BY id DESC
    LIMIT p_limit;
$$ LANGUAGE sql STABLE;

-- ── Overrides ─────────────────────────────────────────────────────────────────

-- Store manual overrides. Overrides stay in their own column — never merged into
-- transformed — so reprocessing rules cannot clobber a manual edit.
DROP FUNCTION IF EXISTS set_record_overrides(INTEGER, JSONB);
CREATE OR REPLACE FUNCTION set_record_overrides(p_id INTEGER, p_overrides JSONB)
RETURNS JSON AS $$
    WITH updated AS (
        UPDATE dataflow.records
        SET overrides = CASE WHEN p_overrides = '{}'::jsonb THEN NULL ELSE p_overrides END
        WHERE id = p_id
        RETURNING *
    )
    SELECT row_to_json(updated) FROM updated;
$$ LANGUAGE sql;

-- Merge overrides into multiple records at once; returns actual updated count
DROP FUNCTION IF EXISTS bulk_set_record_overrides(TEXT, INTEGER[], JSONB);
CREATE OR REPLACE FUNCTION bulk_set_record_overrides(p_source_name TEXT, p_ids INTEGER[], p_overrides JSONB)
RETURNS JSON AS $$
    WITH updated AS (
        UPDATE dataflow.records
        SET overrides = COALESCE(overrides, '{}'::jsonb) || p_overrides
        WHERE id = ANY(p_ids)
          AND source_name = p_source_name
        RETURNING id
    )
    SELECT json_build_object('updated', count(*)) FROM updated;
$$ LANGUAGE sql;

-- Clear overrides; the computed values in transformed are untouched
DROP FUNCTION IF EXISTS clear_record_overrides(INTEGER);
CREATE OR REPLACE FUNCTION clear_record_overrides(p_id INTEGER)
RETURNS JSON AS $$
    WITH updated AS (
        UPDATE dataflow.records
        SET overrides = NULL
        WHERE id = p_id
        RETURNING *
    )
    SELECT row_to_json(updated) FROM updated;
$$ LANGUAGE sql;

-- ── Delete ────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION delete_record(p_id BIGINT)
RETURNS TABLE (id BIGINT) AS $$
    DELETE FROM dataflow.records WHERE id = p_id RETURNING id;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION delete_source_records(p_source_name TEXT)
RETURNS TABLE (deleted_count BIGINT) AS $$
    WITH deleted AS (
        DELETE FROM dataflow.records WHERE source_name = p_source_name RETURNING id
    )
    SELECT count(*) AS deleted_count FROM deleted;
$$ LANGUAGE sql;