-- Build-plan section 2.5: "No source found in this research documents
-- cryptographic timestamping or hashing of collection-point records at any
-- comparable platform; it is a natural, low-cost complement to [independent
-- verification] and is worth specifying as a Verify build item precisely
-- because it appears to be an open design choice across the whole sector."
--
-- A per-tenant hash chain over collection_events: each row's record_hash is
-- a sha256 digest of the previous row's hash plus this row's own immutable
-- fields, so altering or deleting any past CollectionEvent breaks every
-- hash after it — tamper-evidence for exactly the record eFishery's own
-- collapse showed can be fabricated, independent of and in addition to
-- section 2.5's structural verifier-independence trigger. Matches section
-- 7.1's own append-only-event-log framing: a correction is a new event, an
-- edit to history is detectable, not silently possible.
--
-- The hashing logic lives once, in next_collection_event_hash(), called by
-- both the write-side trigger below and verify_collection_event_chain()
-- (verify.ts's GET /collection-events/verify-chain) — a single definition
-- so the two can never silently drift apart and produce a false tamper
-- signal against a real, untouched chain.

alter table collection_events add column record_hash text;

create or replace function next_collection_event_hash(prev_hash text, r collection_events) returns text as $$
begin
  -- extract(epoch from ...) rather than a text cast of the timestamptz
  -- itself: casting timestamptz to text renders in the session's own
  -- TimeZone setting, which is not guaranteed identical between the
  -- session that wrote a row and a later session verifying it — a false
  -- "tamper detected" from a timezone difference alone would be exactly
  -- the kind of silent correctness bug build-plan section 14.4 (Horizon)
  -- warns a ledger-adjacent system must not ship. epoch seconds are
  -- timezone-independent by construction.
  return encode(
    digest(
      prev_hash || '|' || r.tenant_id::text || '|' || r.supplier_id::text || '|' ||
      r.captured_by::text || '|' || r.capture_mode || '|' || r.quantity::text || '|' ||
      coalesce(r.quality_grade, '') || '|' || r.unit_price::text || '|' ||
      extract(epoch from r.occurred_at)::text,
      'sha256'
    ),
    'hex'
  );
end;
$$ language plpgsql immutable;

create or replace function collection_event_chain_genesis(p_tenant_id uuid) returns text as $$
begin
  return encode(digest('genesis:' || p_tenant_id::text, 'sha256'), 'hex');
end;
$$ language plpgsql immutable;

create or replace function compute_collection_event_hash() returns trigger as $$
declare
  prev_hash text;
begin
  select record_hash into prev_hash from collection_events
    where tenant_id = new.tenant_id
    order by created_at desc, id desc
    limit 1;
  if prev_hash is null then
    prev_hash := collection_event_chain_genesis(new.tenant_id);
  end if;
  new.record_hash := next_collection_event_hash(prev_hash, new);
  return new;
end;
$$ language plpgsql;

create trigger collection_event_hash_chain
  before insert on collection_events
  for each row execute function compute_collection_event_hash();

-- Backfill any pre-existing rows (a fresh dev/test database has none, but a
-- database that already has real capture history needs its chain built
-- retroactively, in the same chronological, per-tenant order the trigger
-- above would have produced had it always existed).
do $$
declare
  r collection_events%rowtype;
  prev_hash text;
  cur_tenant uuid;
  seen_tenant boolean := false;
begin
  for r in select * from collection_events order by tenant_id, created_at, id loop
    if not seen_tenant or cur_tenant is distinct from r.tenant_id then
      prev_hash := collection_event_chain_genesis(r.tenant_id);
      cur_tenant := r.tenant_id;
      seen_tenant := true;
    end if;
    prev_hash := next_collection_event_hash(prev_hash, r);
    update collection_events set record_hash = prev_hash where id = r.id;
  end loop;
end $$;

alter table collection_events alter column record_hash set not null;

-- 001_init.sql's blanket per-table grant loop gave app_user update/delete
-- on every table, collection_events included — harmless until now, since
-- no route ever used it (confirmed: no UPDATE or DELETE against
-- collection_events anywhere in src/), but exactly the structural gap
-- section 1.5's own design rule warns against: a hash chain is only real
-- tamper-evidence if the row it protects cannot be altered out from under
-- it by the same credential the application itself runs as. capture is
-- insert-only by the application's own design (section 7.1's append-only
-- event log), so this revoke costs nothing real and closes that gap
-- structurally, not only by convention.
do $$
begin
  if exists (select 1 from pg_roles where rolname = 'app_user') then
    revoke update, delete on collection_events from app_user;
  end if;
end $$;

create or replace function verify_collection_event_chain(p_tenant_id uuid)
returns table(is_valid boolean, checked_count int, broken_at_id uuid) as $$
declare
  r collection_events%rowtype;
  prev_hash text;
  expected text;
  n int := 0;
begin
  for r in select * from collection_events where tenant_id = p_tenant_id order by created_at, id loop
    if prev_hash is null then
      prev_hash := collection_event_chain_genesis(p_tenant_id);
    end if;
    expected := next_collection_event_hash(prev_hash, r);
    n := n + 1;
    if expected <> r.record_hash then
      is_valid := false;
      checked_count := n;
      broken_at_id := r.id;
      return next;
      return;
    end if;
    prev_hash := expected;
  end loop;
  is_valid := true;
  checked_count := n;
  broken_at_id := null;
  return next;
end;
$$ language plpgsql;