begin;

create table if not exists public.admin_users (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role text not null check (role in ('support', 'admin', 'super_admin')),
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table if not exists public.admin_action_logs (
  id bigserial primary key,
  admin_user_id uuid not null references auth.users(id) on delete cascade,
  action text not null,
  target_type text not null,
  target_id text not null default '',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create index if not exists idx_admin_action_logs_admin_created_at
  on public.admin_action_logs(admin_user_id, created_at desc);

create index if not exists idx_admin_action_logs_target
  on public.admin_action_logs(target_type, target_id, created_at desc);

alter table public.admin_users enable row level security;
alter table public.admin_action_logs enable row level security;

create or replace function public.is_admin_user()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1
    from public.admin_users a
    where a.user_id = auth.uid()
      and a.is_active = true
  );
$$;

revoke all on function public.is_admin_user() from public;
grant execute on function public.is_admin_user() to authenticated;
grant execute on function public.is_admin_user() to service_role;

drop policy if exists "admin_users_select_self" on public.admin_users;
create policy "admin_users_select_self"
on public.admin_users
for select
to authenticated
using (user_id = auth.uid());

drop policy if exists "admin_action_logs_select_none" on public.admin_action_logs;
create policy "admin_action_logs_select_none"
on public.admin_action_logs
for select
to authenticated
using (false);

drop policy if exists "admin_action_logs_insert_none" on public.admin_action_logs;
create policy "admin_action_logs_insert_none"
on public.admin_action_logs
for insert
to authenticated
with check (false);

revoke all on table public.admin_users from public;
revoke all on table public.admin_action_logs from public;
grant select on table public.admin_users to authenticated;
grant all on table public.admin_users to service_role;
grant all on table public.admin_action_logs to service_role;

create or replace function public.admin_approve_subscription_request(
  _request_id bigint,
  _months integer default 1,
  _decided_by uuid default null
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  r public.subscription_requests%rowtype;
  v_months integer := greatest(coalesce(_months, 1), 1);
  v_decider uuid := coalesce(_decided_by, auth.uid());
begin
  select *
  into r
  from public.subscription_requests
  where id = _request_id
  for update;

  if not found then
    raise exception 'Request not found';
  end if;

  if r.status <> 'pending' then
    raise exception 'Request % is not pending (current status: %)', r.id, r.status;
  end if;

  update public.subscription_requests
  set status = 'approved',
      decided_at = now(),
      decided_by = v_decider
  where id = _request_id;

  insert into public.shop_entitlements(
    shop_id,
    plan,
    is_active,
    max_customers,
    max_debts,
    expires_at,
    updated_at
  )
  values (
    r.shop_id,
    'pro',
    true,
    2147483647,
    2147483647,
    now() + make_interval(months => v_months),
    now()
  )
  on conflict (shop_id)
  do update set
    plan = 'pro',
    is_active = true,
    max_customers = 2147483647,
    max_debts = 2147483647,
    expires_at = (
      case
        when public.shop_entitlements.expires_at is null
          or public.shop_entitlements.expires_at <= now()
          then now()
        else public.shop_entitlements.expires_at
      end
    ) + make_interval(months => v_months),
    updated_at = now();
end;
$$;

create or replace function public.admin_reject_subscription_request(
  _request_id bigint,
  _reason text default ''::text,
  _decided_by uuid default null
)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
  r public.subscription_requests%rowtype;
  v_decider uuid := coalesce(_decided_by, auth.uid());
begin
  select *
  into r
  from public.subscription_requests
  where id = _request_id
  for update;

  if not found then
    raise exception 'Request not found';
  end if;

  if r.status <> 'pending' then
    raise exception 'Request % is not pending (current status: %)', r.id, r.status;
  end if;

  update public.subscription_requests
  set status = 'rejected',
      decided_at = now(),
      decided_by = v_decider,
      note = case
        when coalesce(_reason, '') = '' then note
        when coalesce(note, '') = '' then 'REJECT_REASON: ' || _reason
        else note || chr(10) || 'REJECT_REASON: ' || _reason
      end
  where id = _request_id;
end;
$$;

revoke all on function public.admin_approve_subscription_request(bigint, integer, uuid) from public;
revoke all on function public.admin_reject_subscription_request(bigint, text, uuid) from public;

grant execute on function public.admin_approve_subscription_request(bigint, integer, uuid) to service_role;
grant execute on function public.admin_reject_subscription_request(bigint, text, uuid) to service_role;

commit;
