--
-- Status tracking: view_generated_at on sources and stacks
-- Cleared by triggers when definitions change; set by API when views are generated.
--

SET search_path TO dataflow, public;

-- Add view_generated_at columns
ALTER TABLE dataflow.sources ADD COLUMN IF NOT EXISTS view_generated_at TIMESTAMPTZ;
ALTER TABLE dataflow.stacks  ADD COLUMN IF NOT EXISTS view_generated_at TIMESTAMPTZ;

------------------------------------------------------
-- Trigger: clear source view_generated_at when config (field definitions) changes
-- Rules and mappings affect transformed data, not view structure — no trigger needed there
------------------------------------------------------
DROP TRIGGER IF EXISTS trg_rules_changed ON dataflow.rules;
DROP TRIGGER IF EXISTS trg_mappings_changed ON dataflow.mappings;
DROP FUNCTION IF EXISTS dataflow.rules_changed();
DROP FUNCTION IF EXISTS dataflow.mappings_changed();

CREATE OR REPLACE FUNCTION dataflow.source_config_changed()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.config IS DISTINCT FROM OLD.config THEN
        NEW.view_generated_at := NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_source_config_changed ON dataflow.sources;
CREATE TRIGGER trg_source_config_changed
BEFORE UPDATE ON dataflow.sources
FOR EACH ROW EXECUTE FUNCTION dataflow.source_config_changed();

------------------------------------------------------
-- Trigger: clear stack view_generated_at when sources change
-- On UPDATE, skip if all view-relevant columns are unchanged (upsert no-ops should not mark stale)
------------------------------------------------------
CREATE OR REPLACE FUNCTION dataflow.stack_sources_changed()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF NEW.field_map        IS NOT DISTINCT FROM OLD.field_map        AND
           NEW.amount_sign      IS NOT DISTINCT FROM OLD.amount_sign      AND
           NEW.balance_offset   IS NOT DISTINCT FROM OLD.balance_offset   AND
           NEW.amount_field     IS NOT DISTINCT FROM OLD.amount_field     AND
           NEW.date_field       IS NOT DISTINCT FROM OLD.date_field       AND
           NEW.seq              IS NOT DISTINCT FROM OLD.seq THEN
            RETURN NULL;
        END IF;
    END IF;
    UPDATE dataflow.stacks SET view_generated_at = NULL
    WHERE name = COALESCE(NEW.stack_name, OLD.stack_name);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_stack_sources_changed ON dataflow.stack_sources;
CREATE TRIGGER trg_stack_sources_changed
AFTER INSERT OR UPDATE OR DELETE ON dataflow.stack_sources
FOR EACH ROW EXECUTE FUNCTION dataflow.stack_sources_changed();

------------------------------------------------------
-- Function: get_status
-- Returns sources and stacks whose view is stale (null or never generated)
------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status()
RETURNS JSON AS $$
DECLARE
    v_sources JSON;
    v_stacks  JSON;
BEGIN
    SELECT COALESCE(json_agg(json_build_object('name', name, 'view_generated_at', view_generated_at) ORDER BY name), '[]'::json)
    INTO v_sources
    FROM dataflow.sources
    WHERE view_generated_at IS NULL;

    SELECT COALESCE(json_agg(json_build_object('name', name, 'view_generated_at', view_generated_at) ORDER BY name), '[]'::json)
    INTO v_stacks
    FROM dataflow.stacks
    WHERE view_generated_at IS NULL;

    RETURN json_build_object('stale_sources', v_sources, 'stale_stacks', v_stacks);
END;
$$ LANGUAGE plpgsql STABLE;