-- Install in a NEW private PostgreSQL database as its administrator.
-- Never install this download in the Flowgrammer website database.
BEGIN;
CREATE SCHEMA fg_finance;
CREATE ROLE fg_finance_runner NOLOGIN;
CREATE ROLE fg_finance_reviewer NOLOGIN;
REVOKE ALL ON SCHEMA fg_finance FROM PUBLIC;
GRANT USAGE ON SCHEMA fg_finance TO fg_finance_runner, fg_finance_reviewer;
CREATE TABLE fg_finance.inputs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  client_id text NOT NULL,
  kind text NOT NULL,
  source_id text NOT NULL,
  revision text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (client_id, kind, source_id, revision)
);
CREATE TABLE fg_finance.decisions (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  input_id bigint NOT NULL REFERENCES fg_finance.inputs,
  client_id text NOT NULL,
  dry_run boolean NOT NULL,
  result jsonb NOT NULL,
  effect_key text,
  status text NOT NULL CHECK(status IN ('dry-run','pending','exception','skipped','approved','dispatching','succeeded','uncertain','rejected')),
  approved_by text,
  approved_at timestamptz,
  dispatch_started_at timestamptz,
  destination_id text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (input_id, dry_run)
);
CREATE UNIQUE INDEX decisions_effect_once ON fg_finance.decisions(client_id,effect_key) WHERE NOT dry_run AND effect_key IS NOT NULL;
CREATE TABLE fg_finance.audit (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  decision_id bigint REFERENCES fg_finance.decisions,
  event text NOT NULL,
  actor text NOT NULL DEFAULT session_user,
  detail jsonb NOT NULL DEFAULT '{}',
  created_at timestamptz NOT NULL DEFAULT now()
);
-- Functions own the queue mutations; the runner has no direct table-write or approval grants.
CREATE FUNCTION fg_finance.pending(p_client text, p_kind text, p_dry boolean, p_limit int)
RETURNS SETOF fg_finance.inputs LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$
 SELECT i.* FROM fg_finance.inputs i WHERE i.client_id = p_client AND i.kind = p_kind
 AND NOT EXISTS (SELECT 1 FROM fg_finance.decisions d WHERE d.input_id=i.id AND d.dry_run=p_dry)
 ORDER BY i.id LIMIT greatest(1,least(p_limit,100));
$$;
CREATE FUNCTION fg_finance.record(p_input bigint, p_client text, p_dry boolean, p_result jsonb)
RETURNS bigint LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$
DECLARE v_id bigint; v_status text; v_effect text; v_source text; v_kind text;
BEGIN
 IF NOT EXISTS (SELECT 1 FROM fg_finance.inputs WHERE id=p_input AND client_id=p_client) THEN RAISE EXCEPTION 'Input/client mismatch'; END IF;
 v_status := CASE WHEN p_result->>'action'='exception' THEN 'exception' WHEN p_result->>'action'='skip' THEN 'skipped' WHEN p_dry THEN 'dry-run' ELSE 'pending' END;
 SELECT source_id,kind INTO v_source,v_kind FROM fg_finance.inputs WHERE id=p_input;
 IF v_status NOT IN ('exception','skipped') AND p_result->>'action' IN ('email','quickbooks','xero') THEN
   v_effect := jsonb_build_array(v_kind,v_source,coalesce(p_result->>'stage','single'))::text;
 END IF;
 -- Serialize identical effects across new revisions and concurrent preparation runs.
 PERFORM pg_advisory_xact_lock(hashtext(p_client), hashtext(coalesce(v_effect,p_input::text)));
 IF NOT p_dry AND v_effect IS NOT NULL AND EXISTS(SELECT 1 FROM fg_finance.decisions WHERE client_id=p_client AND effect_key=v_effect AND NOT dry_run) THEN
   v_status := 'skipped'; v_effect := NULL;
   p_result := jsonb_build_object('action','skip','reason','External effect already prepared for this source and stage');
 END IF;
 INSERT INTO fg_finance.decisions(input_id,client_id,dry_run,result,status,effect_key) VALUES(p_input,p_client,p_dry,p_result,v_status,v_effect)
 ON CONFLICT(input_id,dry_run) DO NOTHING RETURNING id INTO v_id;
 IF v_id IS NOT NULL THEN INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(v_id,v_status,jsonb_build_object('input_id',p_input));
 ELSE SELECT id INTO v_id FROM fg_finance.decisions WHERE input_id=p_input AND dry_run=p_dry; END IF;
 RETURN v_id;
END $$;
CREATE FUNCTION fg_finance.approve(p_id bigint, p_note text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$
BEGIN
 IF length(trim(p_note)) < 10 THEN RAISE EXCEPTION 'Record review evidence in approval note'; END IF;
 UPDATE fg_finance.decisions SET status='approved',approved_by=session_user,approved_at=now()
 WHERE id=p_id AND (status='pending' OR (status='approved' AND approved_at <= now()-interval '24 hours')) AND NOT dry_run AND result->>'action' IN ('email','quickbooks','xero');
 IF NOT FOUND THEN RAISE EXCEPTION 'Only live pending external actions can be approved'; END IF;
 INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(p_id,'approved',jsonb_build_object('note',p_note));
END $$;
CREATE FUNCTION fg_finance.claim(p_client text, p_action text, p_enabled boolean)
RETURNS SETOF fg_finance.decisions LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$
DECLARE v_id bigint;
BEGIN
 IF NOT p_enabled THEN RETURN; END IF;
 SELECT id INTO v_id FROM fg_finance.decisions WHERE client_id=p_client AND status='approved' AND NOT dry_run
 AND result->>'action'=p_action AND approved_by IS NOT NULL AND approved_at > now()-interval '24 hours'
 ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;
 IF v_id IS NULL THEN RETURN; END IF;
 UPDATE fg_finance.decisions SET status='dispatching',dispatch_started_at=now() WHERE id=v_id;
 INSERT INTO fg_finance.audit(decision_id,event) VALUES(v_id,'dispatching');
 RETURN QUERY SELECT * FROM fg_finance.decisions WHERE id=v_id;
END $$;
CREATE FUNCTION fg_finance.finish(p_id bigint, p_client text, p_ok boolean, p_destination text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, fg_finance AS $$
BEGIN
 UPDATE fg_finance.decisions SET status=CASE WHEN p_ok THEN 'succeeded' ELSE 'uncertain' END,
 destination_id=left(p_destination,200) WHERE id=p_id AND client_id=p_client AND status='dispatching';
 IF NOT FOUND THEN RAISE EXCEPTION 'No dispatching action'; END IF;
 INSERT INTO fg_finance.audit(decision_id,event,detail) VALUES(p_id,CASE WHEN p_ok THEN 'succeeded' ELSE 'uncertain' END,jsonb_build_object('destination_id',left(p_destination,200)));
END $$;
REVOKE ALL ON ALL TABLES IN SCHEMA fg_finance FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA fg_finance FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA fg_finance FROM PUBLIC;
GRANT EXECUTE ON FUNCTION fg_finance.pending(text,text,boolean,int),fg_finance.record(bigint,text,boolean,jsonb),fg_finance.claim(text,text,boolean),fg_finance.finish(bigint,text,boolean,text) TO fg_finance_runner;
GRANT SELECT ON ALL TABLES IN SCHEMA fg_finance TO fg_finance_reviewer;
GRANT EXECUTE ON FUNCTION fg_finance.approve(bigint,text) TO fg_finance_reviewer;
COMMIT;
