-- Parcel Empire Phase 5.5 Production Hardening
-- Review before applying to a live Supabase project.

-- 1) Helpful indexes for map, ownership, marketplace and leaderboard queries.
create index if not exists idx_atlas_parcels_owner_id on public.atlas_parcels(owner_id);
create index if not exists idx_atlas_parcels_parcel_id on public.atlas_parcels(parcel_id);
create index if not exists idx_atlas_parcels_lat_lng on public.atlas_parcels(lat, lng);
create index if not exists idx_atlas_marketplace_status on public.atlas_marketplace(status);
create index if not exists idx_atlas_marketplace_seller on public.atlas_marketplace(seller_id);
create index if not exists idx_atlas_leaderboard_score on public.atlas_leaderboard(score desc);
create index if not exists idx_atlas_transactions_user_created on public.atlas_transactions(user_id, created_at desc);
create index if not exists idx_atlas_payout_requests_user_created on public.atlas_payout_requests(user_id, created_at desc);

-- 2) Make sure RLS is enabled on key tables.
alter table if exists public.atlas_wallets enable row level security;
alter table if exists public.atlas_transactions enable row level security;
alter table if exists public.atlas_payout_requests enable row level security;
alter table if exists public.parcel_player_profiles enable row level security;
alter table if exists public.atlas_parcels enable row level security;
alter table if exists public.atlas_marketplace enable row level security;
alter table if exists public.atlas_leaderboard enable row level security;
alter table if exists public.atlas_admins enable row level security;

-- 3) Example owner-read policies. If these already exist under different names, keep your existing verified policies.
do $$ begin
  create policy "wallet owner can read own wallet" on public.atlas_wallets for select using (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

do $$ begin
  create policy "transactions owner can read own transactions" on public.atlas_transactions for select using (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

do $$ begin
  create policy "payout owner can read own requests" on public.atlas_payout_requests for select using (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

-- 4) Public read policies for map/leaderboard data. Keep sensitive fields out of these tables.
do $$ begin
  create policy "signed in users can read parcels" on public.atlas_parcels for select using (auth.role() = 'authenticated');
exception when duplicate_object then null; end $$;

do $$ begin
  create policy "signed in users can read leaderboard" on public.atlas_leaderboard for select using (auth.role() = 'authenticated');
exception when duplicate_object then null; end $$;

-- 5) Admin helper view/function pattern should be reviewed manually.
-- Do not grant broad update/delete access to normal users. Prefer security definer RPCs for economy actions.
