CREATE TABLE br_statements(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,statement_key uuid NOT NULL,account_ref text NOT NULL,currency text NOT NULL CHECK(currency='MZN'),period text NOT NULL,csv_sha256 char(64) NOT NULL,csv_bytes text NOT NULL,opening_minor bigint NOT NULL,closing_minor bigint NOT NULL,entries jsonb NOT NULL,source_hash char(64) NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(id,tenant_id,company_id,unit_id),UNIQUE(tenant_id,company_id,unit_id,statement_key),UNIQUE(tenant_id,company_id,unit_id,account_ref,csv_sha256),FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id));
CREATE TABLE br_events(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,statement_id bigint NOT NULL,version bigint NOT NULL CHECK(version>0),previous_id bigint UNIQUE REFERENCES br_events(id),proposal_id bigint UNIQUE REFERENCES br_events(id),action text NOT NULL CHECK(action IN('import','review_statement','prepare_match','review_match','finalize')),state text NOT NULL CHECK(state IN('imported','statement_reviewed','statement_refused','match_prepared','match_reviewed','match_refused','finalized')),prepared_by bigint NOT NULL REFERENCES users(id),actor_id bigint NOT NULL REFERENCES users(id),data jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(statement_id,version),FOREIGN KEY(statement_id,tenant_id,company_id,unit_id) REFERENCES br_statements(id,tenant_id,company_id,unit_id));
CREATE UNIQUE INDEX br_once_ledger_line ON br_events(tenant_id,company_id,unit_id,((data->>'ledger_line_id')::bigint)) WHERE state='match_reviewed';
CREATE UNIQUE INDEX br_once_statement_row ON br_events(statement_id,(data->>'row_reference')) WHERE state='match_reviewed';
CREATE TABLE br_commands(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),command_key uuid NOT NULL,request_hash char(64) NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key));
CREATE FUNCTION sgf_br_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE permission_name text;lastrow br_events;p br_events;header br_statements;
BEGIN
 PERFORM sgf_wo_environment();PERFORM 1 FROM companies WHERE tenant_id=NEW.tenant_id AND id=NEW.company_id FOR UPDATE;
 IF TG_TABLE_NAME='br_statements' THEN permission_name:='bank_reconciliation.import';ELSE permission_name:='bank_reconciliation.'||CASE NEW.action WHEN 'import' THEN 'import' WHEN 'prepare_match' THEN 'prepare' ELSE 'review' END;END IF;
 IF NOT EXISTS(SELECT 1 FROM company_memberships WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id) OR NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id AND unit_id=NEW.unit_id AND permission=permission_name) THEN RAISE EXCEPTION 'SGF_BR_ACCESS';END IF;
 IF TG_TABLE_NAME='br_statements' THEN RETURN NEW;END IF;
 SELECT * INTO header FROM br_statements WHERE id=NEW.statement_id AND (tenant_id,company_id,unit_id)=(NEW.tenant_id,NEW.company_id,NEW.unit_id);IF header.id IS NULL THEN RAISE EXCEPTION 'SGF_BR_SCOPE';END IF;
 SELECT * INTO lastrow FROM br_events WHERE statement_id=NEW.statement_id ORDER BY version DESC LIMIT 1;IF NEW.version<>coalesce(lastrow.version,0)+1 OR NEW.previous_id IS DISTINCT FROM lastrow.id OR EXISTS(SELECT 1 FROM br_events WHERE statement_id=NEW.statement_id AND state='finalized') THEN RAISE EXCEPTION 'SGF_BR_STALE_OR_FINAL';END IF;
 IF NEW.action='import' THEN IF lastrow.id IS NOT NULL OR NEW.state<>'imported' OR NEW.actor_id<>header.actor_id THEN RAISE EXCEPTION 'SGF_BR_IMPORT';END IF;
 ELSIF NEW.action='review_statement' THEN IF NEW.actor_id=header.actor_id OR NEW.prepared_by<>header.actor_id OR NEW.state NOT IN('statement_reviewed','statement_refused') OR EXISTS(SELECT 1 FROM br_events WHERE statement_id=NEW.statement_id AND action='review_statement') THEN RAISE EXCEPTION 'SGF_BR_REVIEW';END IF;
 ELSE IF NOT EXISTS(SELECT 1 FROM br_events WHERE statement_id=NEW.statement_id AND state='statement_reviewed') THEN RAISE EXCEPTION 'SGF_BR_NOT_REVIEWED';END IF;
 IF NEW.action='prepare_match' THEN IF NEW.actor_id<>NEW.prepared_by OR NEW.state<>'match_prepared' THEN RAISE EXCEPTION 'SGF_BR_PREPARE';END IF;
 ELSIF NEW.action='review_match' THEN SELECT * INTO p FROM br_events WHERE id=NEW.proposal_id AND statement_id=NEW.statement_id AND state='match_prepared';IF p.id IS NULL OR NEW.actor_id=p.actor_id OR NEW.prepared_by<>p.prepared_by OR NEW.data IS DISTINCT FROM p.data OR NEW.state NOT IN('match_reviewed','match_refused') THEN RAISE EXCEPTION 'SGF_BR_MATCH_REVIEW';END IF;
 ELSIF NEW.action='finalize' THEN IF NEW.actor_id=header.actor_id OR NEW.state<>'finalized' OR (SELECT count(*) FROM br_events WHERE statement_id=NEW.statement_id AND state='match_reviewed')<>jsonb_array_length(header.entries) OR EXISTS(SELECT 1 FROM br_events proposed WHERE proposed.statement_id=NEW.statement_id AND proposed.state='match_prepared' AND NOT EXISTS(SELECT 1 FROM br_events r WHERE r.proposal_id=proposed.id)) THEN RAISE EXCEPTION 'SGF_BR_INCOMPLETE';END IF;END IF;END IF;
 RETURN NEW;
END; $$;
CREATE TRIGGER br_statement_guard BEFORE INSERT ON br_statements FOR EACH ROW EXECUTE FUNCTION sgf_br_guard();
CREATE TRIGGER br_event_guard BEFORE INSERT ON br_events FOR EACH ROW EXECUTE FUNCTION sgf_br_guard();
CREATE TRIGGER br_statements_immutable BEFORE UPDATE OR DELETE ON br_statements FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
CREATE TRIGGER br_events_immutable BEFORE UPDATE OR DELETE ON br_events FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
CREATE TRIGGER br_commands_immutable BEFORE UPDATE OR DELETE ON br_commands FOR EACH ROW EXECUTE FUNCTION sgf_append_only_audit();
CREATE TRIGGER br_commands_test BEFORE INSERT ON br_commands FOR EACH ROW EXECUTE FUNCTION sgf_wo_test_only();
REVOKE ALL ON br_statements,br_events,br_commands FROM PUBLIC;
REVOKE ALL ON SEQUENCE br_statements_id_seq,br_events_id_seq,br_commands_id_seq FROM PUBLIC;
REVOKE ALL ON FUNCTION sgf_br_guard() FROM PUBLIC;