-- =====================================================================
-- Migration 0005 — Inventory and e-float (roadmap Step 6).
--
-- Two parallel stock systems for a cell shop:
--
--   1. Physical inventory: scratch cards (with serials), SIMs, phones,
--      accessories. Voucher serials track per-card lifecycle so the
--      same card can never be sold twice and "lost" cards are visible.
--
--   2. Electronic float: OMT cash float, Alfa/touch e-recharge wallet,
--      whish, etc. Every recharge or transfer must move e-float in
--      lockstep with cash, otherwise reconciliation fails.
--
-- Threat-model rows addressed: 3, 4, 5, 13, 21, 22.
-- =====================================================================

-- =====================================================================
-- Items and physical stock
-- =====================================================================
do $$ begin
  create type app.item_type as enum (
    'scratch_card', 'sim', 'phone', 'accessory', 'consumable'
  );
exception when duplicate_object then null; end $$;

do $$ begin
  create type app.stock_movement_type as enum (
    'purchase_in',     -- received from distributor
    'sale_out',        -- linked to a transaction
    'return_in',       -- customer return
    'damaged_out',     -- write-off (manager approval)
    'lost_out',        -- write-off (manager approval)
    'transfer_in',     -- between shops
    'transfer_out',
    'adjustment_in',   -- audited correction
    'adjustment_out'
  );
exception when duplicate_object then null; end $$;

do $$ begin
  create type app.voucher_status as enum (
    'in_stock', 'sold', 'damaged', 'lost', 'returned'
  );
exception when duplicate_object then null; end $$;

create table if not exists app.items (
  sku             text primary key,
  name            text not null,
  type            app.item_type not null,
  operator        text,                            -- 'ALFA','TOUCH', null for non-recharge
  face_value_usd  numeric(14,2),                   -- recharge denomination if applicable
  cost_usd        numeric(14,2) not null check (cost_usd >= 0),
  price_usd       numeric(14,2) not null check (price_usd >= 0),
  is_active       boolean not null default true,
  created_at      timestamptz not null default now()
);

-- Per-shop stock-on-hand counter (denormalized, kept in sync by trigger).
create table if not exists app.stock_on_hand (
  sku        text not null references app.items(sku),
  shop_id    uuid not null references app.shops(id),
  qty        integer not null default 0 check (qty >= 0),
  primary key (sku, shop_id)
);

create table if not exists app.stock_lots (
  id              uuid primary key default gen_random_uuid(),
  sku             text not null references app.items(sku),
  shop_id         uuid not null references app.shops(id),
  received_at     timestamptz not null default now(),
  qty_received    integer not null check (qty_received > 0),
  unit_cost_usd   numeric(14,2) not null check (unit_cost_usd >= 0),
  supplier        text,
  invoice_no      text,
  received_by     uuid not null references auth.users(id) default auth.uid(),
  created_at      timestamptz not null default now()
);
create index if not exists idx_stock_lots_sku_shop on app.stock_lots(sku, shop_id);

-- Append-only stock movements ledger -----------------------------------
create table if not exists app.stock_movements (
  id            uuid primary key default gen_random_uuid(),
  sku           text not null references app.items(sku),
  shop_id       uuid not null references app.shops(id),
  shift_id      uuid references app.shifts(id),
  type          app.stock_movement_type not null,
  -- Signed: positive = +stock (purchase_in, return_in, transfer_in, adjustment_in)
  --         negative = -stock (sale_out, damaged_out, lost_out, transfer_out, adjustment_out)
  qty_delta     integer not null check (qty_delta <> 0),
  ref_txn_id    uuid references app.transactions(id),
  ref_lot_id    uuid references app.stock_lots(id),
  approved_by   uuid references auth.users(id),     -- required for damaged/lost/adjustment
  reason        text,
  created_at    timestamptz not null default now(),
  created_by    uuid not null references auth.users(id) default auth.uid()
);
create index if not exists idx_stock_mov_sku_shop on app.stock_movements(sku, shop_id, created_at desc);
create index if not exists idx_stock_mov_txn      on app.stock_movements(ref_txn_id);

-- =====================================================================
-- Voucher inventory (per-serial lifecycle)
-- =====================================================================
create table if not exists app.voucher_inventory (
  serial       text primary key,
  sku          text not null references app.items(sku),
  shop_id      uuid not null references app.shops(id),
  lot_id       uuid references app.stock_lots(id),
  status       app.voucher_status not null default 'in_stock',
  received_at  timestamptz not null default now(),
  sold_txn_id  uuid references app.transactions(id),
  sold_at      timestamptz,
  status_changed_by uuid references auth.users(id),
  status_change_reason text,
  constraint voucher_status_consistency check (
       (status = 'in_stock' and sold_txn_id is null  and sold_at is null)
    or (status = 'sold'     and sold_txn_id is not null and sold_at is not null)
    or (status in ('damaged','lost','returned')
        and sold_txn_id is null and sold_at is null)
  )
);
create index if not exists idx_voucher_status   on app.voucher_inventory(status);
create index if not exists idx_voucher_sku_shop on app.voucher_inventory(sku, shop_id);

-- =====================================================================
-- E-float (OMT cash float, Alfa e-recharge wallet, etc.)
-- =====================================================================
do $$ begin
  create type app.float_provider as enum (
    'OMT_CASH', 'OMT_DIGITAL', 'ALFA_ERECHARGE', 'TOUCH_ERECHARGE',
    'OGERO_ERECHARGE', 'WHISH', 'CARD_TERMINAL', 'BANK'
  );
exception when duplicate_object then null; end $$;

create table if not exists app.floats (
  id          uuid primary key default gen_random_uuid(),
  shop_id     uuid not null references app.shops(id) on delete restrict,
  provider    app.float_provider not null,
  currency    app.currency_code not null,
  is_active   boolean not null default true,
  created_at  timestamptz not null default now(),
  unique (shop_id, provider, currency)
);

-- Cached balance per float, kept in sync by the movements trigger.
create table if not exists app.float_balances (
  float_id    uuid primary key references app.floats(id) on delete cascade,
  balance     numeric(20,2) not null default 0,
  updated_at  timestamptz not null default now()
);

create table if not exists app.float_movements (
  id            uuid primary key default gen_random_uuid(),
  float_id      uuid not null references app.floats(id) on delete restrict,
  shift_id      uuid references app.shifts(id),
  occurred_at   timestamptz not null default now(),
  -- Signed: + adds to e-float, - removes from it.
  amount        numeric(20,2) not null check (amount <> 0),
  ref_txn_id    uuid references app.transactions(id),
  ref_settlement_id uuid,                            -- FK added in 0007
  reason        text,
  created_at    timestamptz not null default now(),
  created_by    uuid not null references auth.users(id) default auth.uid()
);
create index if not exists idx_float_mov_float on app.float_movements(float_id, occurred_at);
create index if not exists idx_float_mov_txn   on app.float_movements(ref_txn_id);

-- =====================================================================
-- Triggers — append-only, balance maintenance, no negative stock
-- =====================================================================

-- Stock movements: append-only.
create or replace function app._stock_mov_no_update_delete()
returns trigger language plpgsql as $$
begin raise exception 'stock_movements is append-only'; end;
$$;
drop trigger if exists trg_stock_mov_freeze on app.stock_movements;
create trigger trg_stock_mov_freeze before update or delete on app.stock_movements
  for each row execute function app._stock_mov_no_update_delete();

-- Stock movements: server-stamped, sign matches type, optional manager
-- approval enforced for write-offs.
create or replace function app._stock_mov_before_insert()
returns trigger language plpgsql as $$
begin
  new.created_at := now();
  new.created_by := auth.uid();

  -- Sign / type consistency.
  if new.type in ('purchase_in','return_in','transfer_in','adjustment_in')
     and new.qty_delta <= 0 then
    raise exception '% must have qty_delta > 0', new.type;
  end if;
  if new.type in ('sale_out','damaged_out','lost_out','transfer_out','adjustment_out')
     and new.qty_delta >= 0 then
    raise exception '% must have qty_delta < 0', new.type;
  end if;

  -- Write-offs and adjustments need manager approval.
  if new.type in ('damaged_out','lost_out','adjustment_in','adjustment_out')
     and new.approved_by is null then
    raise exception '% requires manager approval (approved_by)', new.type;
  end if;

  -- sale_out must reference a real, completed sale of the same shop.
  if new.type = 'sale_out' then
    if new.ref_txn_id is null then
      raise exception 'sale_out requires ref_txn_id';
    end if;
    if not exists (
      select 1 from app.transactions
      where id = new.ref_txn_id and shop_id = new.shop_id and status = 'completed'
    ) then
      raise exception 'sale_out must reference a completed txn in the same shop';
    end if;
  end if;
  return new;
end;
$$;
drop trigger if exists trg_stock_mov_before_insert on app.stock_movements;
create trigger trg_stock_mov_before_insert before insert on app.stock_movements
  for each row execute function app._stock_mov_before_insert();

-- Maintain stock_on_hand. No negative balance allowed.
create or replace function app._stock_on_hand_apply()
returns trigger language plpgsql as $$
begin
  insert into app.stock_on_hand(sku, shop_id, qty)
    values (new.sku, new.shop_id, new.qty_delta)
  on conflict (sku, shop_id) do update
    set qty = app.stock_on_hand.qty + new.qty_delta;
  -- Re-check; the CHECK on the table will already reject negatives but
  -- give a clearer error here.
  if (select qty from app.stock_on_hand
      where sku = new.sku and shop_id = new.shop_id) < 0 then
    raise exception 'stock would go negative for sku=% shop=%', new.sku, new.shop_id;
  end if;
  return null;
end;
$$;
drop trigger if exists trg_stock_on_hand_apply on app.stock_movements;
create trigger trg_stock_on_hand_apply after insert on app.stock_movements
  for each row execute function app._stock_on_hand_apply();

-- Stock_lots: receiving stock auto-creates a purchase_in movement.
create or replace function app._stock_lot_after_insert()
returns trigger language plpgsql as $$
begin
  insert into app.stock_movements(sku, shop_id, type, qty_delta, ref_lot_id, reason)
    values (new.sku, new.shop_id, 'purchase_in', new.qty_received, new.id,
            coalesce('lot ' || new.invoice_no, 'lot received'));
  return null;
end;
$$;
drop trigger if exists trg_stock_lot_after_insert on app.stock_lots;
create trigger trg_stock_lot_after_insert after insert on app.stock_lots
  for each row execute function app._stock_lot_after_insert();

-- Float movements: append-only + balance.
create or replace function app._float_mov_no_update_delete()
returns trigger language plpgsql as $$
begin raise exception 'float_movements is append-only'; end;
$$;
drop trigger if exists trg_float_mov_freeze on app.float_movements;
create trigger trg_float_mov_freeze before update or delete on app.float_movements
  for each row execute function app._float_mov_no_update_delete();

create or replace function app._float_balance_apply()
returns trigger language plpgsql as $$
begin
  insert into app.float_balances(float_id, balance, updated_at)
    values (new.float_id, new.amount, now())
  on conflict (float_id) do update
    set balance    = app.float_balances.balance + new.amount,
        updated_at = now();
  if (select balance from app.float_balances where float_id = new.float_id) < 0 then
    raise exception 'float would go negative for float_id=%', new.float_id;
  end if;
  return null;
end;
$$;
drop trigger if exists trg_float_balance_apply on app.float_movements;
create trigger trg_float_balance_apply after insert on app.float_movements
  for each row execute function app._float_balance_apply();

-- =====================================================================
-- Recharge ↔ stock/float coupling
-- A recharge_details row MUST move either physical stock (voucher) or
-- e-float, otherwise it is a free recharge — exactly the fraud we want
-- to make impossible (vectors #3, #21).
-- Implemented as a deferred constraint trigger so the client can write
-- the recharge row first, then the movement, in a single transaction.
-- =====================================================================
create or replace function app._recharge_require_movement()
returns trigger
language plpgsql
as $$
declare
  has_voucher_movement boolean;
  has_float_movement   boolean;
  v_provider           app.float_provider;
begin
  if new.voucher_serial is not null then
    -- The voucher must be marked sold and tied to this txn.
    select exists(
      select 1 from app.voucher_inventory
      where serial = new.voucher_serial
        and status = 'sold'
        and sold_txn_id = new.txn_id
    ) into has_voucher_movement;
    if not has_voucher_movement then
      raise exception
        'recharge with voucher_serial=% must be paired with a sold voucher',
        new.voucher_serial;
    end if;
  else
    -- E-recharge: an e-float debit must exist for this txn against the
    -- matching operator's e-float account.
    v_provider := case new.operator
      when 'ALFA'     then 'ALFA_ERECHARGE'::app.float_provider
      when 'TOUCH'    then 'TOUCH_ERECHARGE'::app.float_provider
      when 'OGERO'    then 'OGERO_ERECHARGE'::app.float_provider
      else null
    end;
    if v_provider is null then
      -- Unmapped operator (IDM, CYBERIA, TERRANET): require any negative
      -- float movement for this txn.
      select exists(
        select 1 from app.float_movements
        where ref_txn_id = new.txn_id and amount < 0
      ) into has_float_movement;
    else
      select exists(
        select 1
        from app.float_movements fm
        join app.floats f on f.id = fm.float_id
        join app.transactions t on t.id = fm.ref_txn_id
        where fm.ref_txn_id = new.txn_id
          and fm.amount < 0
          and f.provider = v_provider
          and f.shop_id  = t.shop_id
      ) into has_float_movement;
    end if;
    if not has_float_movement then
      raise exception
        'e-recharge txn % must be paired with a negative e-float movement',
        new.txn_id;
    end if;
  end if;
  return null;
end;
$$;

drop trigger if exists trg_recharge_require_movement on app.recharge_details;
create constraint trigger trg_recharge_require_movement
  after insert on app.recharge_details
  deferrable initially deferred
  for each row execute function app._recharge_require_movement();

-- Goods sale ↔ stock_movement coupling (same idea).
create or replace function app._goods_sale_require_movement()
returns trigger
language plpgsql
as $$
declare ok boolean;
begin
  select exists(
    select 1 from app.stock_movements sm
    where sm.ref_txn_id = new.txn_id
      and sm.sku        = new.sku
      and sm.type       = 'sale_out'
      and -sm.qty_delta = new.qty
  ) into ok;
  if not ok then
    raise exception 'goods sale txn % must be paired with a sale_out stock movement', new.txn_id;
  end if;
  return null;
end;
$$;
drop trigger if exists trg_goods_sale_require_movement on app.goods_sale_details;
create constraint trigger trg_goods_sale_require_movement
  after insert on app.goods_sale_details
  deferrable initially deferred
  for each row execute function app._goods_sale_require_movement();

-- =====================================================================
-- SECURITY DEFINER helpers used by the cashier UI
-- =====================================================================

-- Sell a scratch card: marks the voucher sold + creates the stock_out.
-- Called inside the same transaction as inserting the txn + recharge_details.
create or replace function app.sell_voucher(
  p_txn_id uuid,
  p_serial text
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare v app.voucher_inventory%rowtype;
        t app.transactions%rowtype;
begin
  select * into t from app.transactions where id = p_txn_id;
  if t.id is null then raise exception 'txn not found'; end if;
  if t.user_id <> auth.uid() then
    raise exception 'only the txn owner may sell a voucher against it';
  end if;

  select * into v from app.voucher_inventory where serial = p_serial for update;
  if v.serial is null then raise exception 'voucher % not found', p_serial; end if;
  if v.shop_id <> t.shop_id then
    raise exception 'voucher belongs to a different shop';
  end if;
  if v.status <> 'in_stock' then
    raise exception 'voucher % is not in_stock (status=%)', p_serial, v.status;
  end if;

  update app.voucher_inventory
    set status = 'sold', sold_txn_id = p_txn_id, sold_at = now(),
        status_changed_by = auth.uid()
    where serial = p_serial;

  insert into app.stock_movements(sku, shop_id, shift_id, type, qty_delta, ref_txn_id, reason)
    values (v.sku, v.shop_id, t.shift_id, 'sale_out', -1, p_txn_id, 'voucher ' || p_serial);
end;
$$;

-- Mark a voucher damaged or lost (manager only, with PIN).
create or replace function app.write_off_voucher(
  p_serial text,
  p_status app.voucher_status,
  p_reason text,
  p_manager_pin text
) returns void
language plpgsql
security definer
set search_path = app, public
as $$
declare v app.voucher_inventory%rowtype;
begin
  if p_status not in ('damaged','lost') then
    raise exception 'only damaged/lost are valid write-off statuses';
  end if;
  if p_reason is null or length(btrim(p_reason)) < 5 then
    raise exception 'reason >= 5 chars required';
  end if;
  select * into v from app.voucher_inventory where serial = p_serial for update;
  if v.serial is null then raise exception 'voucher not found'; end if;
  if v.status <> 'in_stock' then
    raise exception 'voucher must be in_stock to write off (was %)', v.status;
  end if;
  if not app.has_role_in_shop(v.shop_id, 'manager') then
    raise exception 'manager role required';
  end if;
  if not app.verify_my_pin(p_manager_pin) then
    raise exception 'invalid manager PIN';
  end if;

  update app.voucher_inventory
    set status = p_status, status_changed_by = auth.uid(),
        status_change_reason = p_reason
    where serial = p_serial;

  insert into app.stock_movements(sku, shop_id, type, qty_delta, approved_by, reason)
    values (v.sku, v.shop_id,
            case p_status when 'damaged' then 'damaged_out'::app.stock_movement_type
                          when 'lost'    then 'lost_out'::app.stock_movement_type end,
            -1, auth.uid(), p_reason);
end;
$$;

revoke all on function app.sell_voucher(uuid, text)                 from public;
revoke all on function app.write_off_voucher(text, app.voucher_status, text, text) from public;
grant execute on function app.sell_voucher(uuid, text)              to authenticated;
grant execute on function app.write_off_voucher(text, app.voucher_status, text, text) to authenticated;

-- =====================================================================
-- RLS
-- =====================================================================
alter table app.items             enable row level security;
alter table app.stock_on_hand     enable row level security;
alter table app.stock_lots        enable row level security;
alter table app.stock_movements   enable row level security;
alter table app.voucher_inventory enable row level security;
alter table app.floats            enable row level security;
alter table app.float_balances    enable row level security;
alter table app.float_movements   enable row level security;

alter table app.items             force row level security;
alter table app.stock_on_hand     force row level security;
alter table app.stock_lots        force row level security;
alter table app.stock_movements   force row level security;
alter table app.voucher_inventory force row level security;
alter table app.floats            force row level security;
alter table app.float_balances    force row level security;
alter table app.float_movements   force row level security;

-- Block direct UPDATE/DELETE on append-only tables.
revoke update, delete on app.stock_movements   from authenticated;
revoke update, delete on app.float_movements   from authenticated;
revoke update, delete on app.voucher_inventory from authenticated;
revoke update, delete on app.stock_on_hand     from authenticated;
revoke update, delete on app.float_balances    from authenticated;
-- Items are reference data: only owners may modify (handled by policy).

-- Items: readable by everyone authenticated; writes for owners only.
drop policy if exists items_select on app.items;
create policy items_select on app.items for select to authenticated using (true);
drop policy if exists items_write_owner on app.items;
create policy items_write_owner on app.items
  for all to authenticated
  using (app.is_owner_anywhere())
  with check (app.is_owner_anywhere());

-- Shop-scoped tables: readable to anyone assigned to the shop.
drop policy if exists soh_select on app.stock_on_hand;
create policy soh_select on app.stock_on_hand
  for select to authenticated
  using (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','cashier','auditor']::app.business_role[])
  );

drop policy if exists lots_select on app.stock_lots;
create policy lots_select on app.stock_lots
  for select to authenticated
  using (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','auditor']::app.business_role[])
  );
drop policy if exists lots_insert on app.stock_lots;
create policy lots_insert on app.stock_lots
  for insert to authenticated
  with check (app.has_any_role_in_shop(shop_id, array['owner','manager']::app.business_role[]));
grant select, insert on app.stock_lots to authenticated;

drop policy if exists smov_select on app.stock_movements;
create policy smov_select on app.stock_movements
  for select to authenticated
  using (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','cashier','auditor']::app.business_role[])
  );
drop policy if exists smov_insert on app.stock_movements;
create policy smov_insert on app.stock_movements
  for insert to authenticated
  with check (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','cashier']::app.business_role[])
  );
grant select, insert on app.stock_movements to authenticated;

drop policy if exists vouch_select on app.voucher_inventory;
create policy vouch_select on app.voucher_inventory
  for select to authenticated
  using (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','cashier','auditor']::app.business_role[])
  );
drop policy if exists vouch_insert on app.voucher_inventory;
create policy vouch_insert on app.voucher_inventory
  for insert to authenticated
  with check (app.has_any_role_in_shop(shop_id, array['owner','manager']::app.business_role[]));
grant select, insert on app.voucher_inventory to authenticated;
-- Voucher status changes go through SECURITY DEFINER functions only.

drop policy if exists floats_select on app.floats;
create policy floats_select on app.floats
  for select to authenticated
  using (
    app.has_any_role_in_shop(shop_id,
      array['owner','manager','cashier','auditor']::app.business_role[])
  );
drop policy if exists floats_write_owner on app.floats;
create policy floats_write_owner on app.floats
  for all to authenticated
  using (app.has_role_in_shop(shop_id, 'owner'))
  with check (app.has_role_in_shop(shop_id, 'owner'));
grant select, insert, update on app.floats to authenticated;

drop policy if exists fbal_select on app.float_balances;
create policy fbal_select on app.float_balances
  for select to authenticated
  using (
    exists (
      select 1 from app.floats f
      where f.id = float_balances.float_id
        and app.has_any_role_in_shop(f.shop_id,
              array['owner','manager','cashier','auditor']::app.business_role[])
    )
  );

drop policy if exists fmov_select on app.float_movements;
create policy fmov_select on app.float_movements
  for select to authenticated
  using (
    exists (
      select 1 from app.floats f
      where f.id = float_movements.float_id
        and app.has_any_role_in_shop(f.shop_id,
              array['owner','manager','cashier','auditor']::app.business_role[])
    )
  );
drop policy if exists fmov_insert on app.float_movements;
create policy fmov_insert on app.float_movements
  for insert to authenticated
  with check (
    exists (
      select 1 from app.floats f
      where f.id = float_movements.float_id
        and app.has_any_role_in_shop(f.shop_id,
              array['owner','manager','cashier']::app.business_role[])
    )
  );
grant select, insert on app.float_movements to authenticated;

grant select on app.items, app.stock_on_hand, app.float_balances to authenticated;

-- End migration 0005 ----------------------------------------------------