-- Grow's loyalty and incentive engine (build-plan section 4.4): "separating
-- IncentiveProgramme from StandingScore" so a reward programme is its own
-- configurable object, not scoring logic entangled with payout logic.
-- Sequenced as the anchor-funded-premium case specifically (no new
-- integration required) - the plan's own first step before a DFI
-- partnership or external carbon-registry integration, both deliberately
-- deferred (section 4.5).

create table incentive_programmes (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  name text not null,
  reward_description text not null,
  standing_score_threshold numeric(7,3) not null check (standing_score_threshold >= 0),
  active boolean not null default true,
  created_at timestamptz not null default now()
);

-- One award per (programme, supplier): a Supplier is recognised for
-- crossing a programme's threshold once, not repeatedly on every
-- subsequent StandingScore recompute that still happens to clear it.
create table incentive_awards (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references tenants(id),
  programme_id uuid not null references incentive_programmes(id),
  supplier_id uuid not null references suppliers(id),
  standing_score_at_award numeric(7,3) not null,
  awarded_at timestamptz not null default now(),
  unique (programme_id, supplier_id)
);

do $$
declare
  t text;
begin
  foreach t in array array['incentive_programmes', 'incentive_awards']
  loop
    execute format('alter table %I enable row level security', t);
    execute format('alter table %I force row level security', t);
    execute format(
      'create policy tenant_isolation_%1$s on %1$I using (
         nullif(current_setting(''app.current_tenant_id'', true), '''') is null
         or tenant_id::text = current_setting(''app.current_tenant_id'', true)
       )', t
    );
    if exists (select 1 from pg_roles where rolname = 'app_user') then
      execute format('grant select, insert, update, delete on %I to app_user', t);
    end if;
  end loop;
end
$$;