-- =====================================================
-- PARCEL EMPIRE - PHASE 2.6 SUPABASE SCHEMA
-- Real accounts, cloud save, global parcel ownership,
-- global leaderboard, marketplace, server wallet, player profiles, and secure upgrades.
-- Run this inside Supabase SQL Editor.
-- =====================================================

create extension if not exists pgcrypto;

create table if not exists public.atlas_profiles (
    id uuid primary key references auth.users(id) on delete cascade,
    email text,
    display_name text,
    player_state jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_profiles enable row level security;

drop policy if exists "Players can read their own profile" on public.atlas_profiles;
drop policy if exists "Players can insert their own profile" on public.atlas_profiles;
drop policy if exists "Players can update their own profile" on public.atlas_profiles;

create policy "Players can read their own profile"
on public.atlas_profiles
for select
to authenticated
using (auth.uid() = id);

create policy "Players can insert their own profile"
on public.atlas_profiles
for insert
to authenticated
with check (auth.uid() = id);

create policy "Players can update their own profile"
on public.atlas_profiles
for update
to authenticated
using (auth.uid() = id)
with check (auth.uid() = id);

-- Global parcel ownership. parcel_id is the real map grid coordinate ID.
-- The primary key stops two users from owning the same parcel.
create table if not exists public.atlas_parcels (
    parcel_id text primary key,
    owner_id uuid references auth.users(id) on delete set null,
    owner_name text,
    parcel_data jsonb not null default '{}'::jsonb,
    income_per_second numeric not null default 0,
    bought_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_parcels enable row level security;

create index if not exists atlas_parcels_owner_id_idx on public.atlas_parcels(owner_id);
create index if not exists atlas_parcels_updated_at_idx on public.atlas_parcels(updated_at desc);

drop policy if exists "Anyone signed in can read parcel ownership" on public.atlas_parcels;
drop policy if exists "Public can read global parcel ownership" on public.atlas_parcels;
drop policy if exists "Players can insert parcels they own" on public.atlas_parcels;
drop policy if exists "Players can update parcels they own" on public.atlas_parcels;

-- Public read keeps the map useful for signed-out visitors too.
-- It only exposes parcel ownership/game data, not private profile saves.
create policy "Public can read global parcel ownership"
on public.atlas_parcels
for select
to anon, authenticated
using (true);

create policy "Players can insert parcels they own"
on public.atlas_parcels
for insert
to authenticated
with check (auth.uid() = owner_id);

create policy "Players can update parcels they own"
on public.atlas_parcels
for update
to authenticated
using (auth.uid() = owner_id)
with check (auth.uid() = owner_id);

-- Global leaderboard / player directory.
-- This contains public game stats only. Private cloud saves remain in atlas_profiles.
create table if not exists public.atlas_leaderboard (
    user_id uuid primary key references auth.users(id) on delete cascade,
    display_name text not null default 'Atlas Player',
    score integer not null default 0,
    parcels integer not null default 0,
    income_per_second numeric not null default 0,
    lifetime_earned numeric not null default 0,
    upgraded_parcels integer not null default 0,
    empire_level integer not null default 1,
    last_seen timestamptz not null default now(),
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_leaderboard enable row level security;

create index if not exists atlas_leaderboard_score_idx on public.atlas_leaderboard(score desc);
create index if not exists atlas_leaderboard_display_name_idx on public.atlas_leaderboard(display_name);
create index if not exists atlas_leaderboard_last_seen_idx on public.atlas_leaderboard(last_seen desc);

drop policy if exists "Public can read atlas leaderboard" on public.atlas_leaderboard;
drop policy if exists "Players can insert their own leaderboard row" on public.atlas_leaderboard;
drop policy if exists "Players can update their own leaderboard row" on public.atlas_leaderboard;

create policy "Public can read atlas leaderboard"
on public.atlas_leaderboard
for select
to anon, authenticated
using (true);

create policy "Players can insert their own leaderboard row"
on public.atlas_leaderboard
for insert
to authenticated
with check (auth.uid() = user_id);

create policy "Players can update their own leaderboard row"
on public.atlas_leaderboard
for update
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);


-- =====================================================
-- Player marketplace / parcel trading.
-- =====================================================
create table if not exists public.atlas_marketplace (
    id uuid primary key default gen_random_uuid(),
    parcel_id text not null references public.atlas_parcels(parcel_id) on delete cascade,
    seller_id uuid not null references auth.users(id) on delete cascade,
    seller_name text not null default 'Atlas Player',
    buyer_id uuid references auth.users(id) on delete set null,
    price numeric not null check (price > 0),
    status text not null default 'active' check (status in ('active', 'sold', 'cancelled')),
    parcel_snapshot jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    sold_at timestamptz
);

alter table public.atlas_marketplace enable row level security;

create index if not exists atlas_marketplace_status_idx on public.atlas_marketplace(status, created_at desc);
create index if not exists atlas_marketplace_parcel_id_idx on public.atlas_marketplace(parcel_id);
create index if not exists atlas_marketplace_seller_id_idx on public.atlas_marketplace(seller_id);
create index if not exists atlas_marketplace_buyer_id_idx on public.atlas_marketplace(buyer_id);

-- Only allow one active listing per parcel.
create unique index if not exists atlas_marketplace_one_active_listing_per_parcel
on public.atlas_marketplace(parcel_id)
where status = 'active';

drop policy if exists "Public can read marketplace listings" on public.atlas_marketplace;
drop policy if exists "Players can list parcels they own" on public.atlas_marketplace;
drop policy if exists "Players can update their own listings" on public.atlas_marketplace;

create policy "Public can read marketplace listings"
on public.atlas_marketplace
for select
to anon, authenticated
using (true);

create policy "Players can list parcels they own"
on public.atlas_marketplace
for insert
to authenticated
with check (
    auth.uid() = seller_id
    and exists (
        select 1
        from public.atlas_parcels p
        where p.parcel_id = atlas_marketplace.parcel_id
        and p.owner_id = auth.uid()
    )
);

create policy "Players can update their own listings"
on public.atlas_marketplace
for update
to authenticated
using (auth.uid() = seller_id)
with check (auth.uid() = seller_id);

-- Safe server-side ownership transfer used when a marketplace listing is bought.
-- Prototype note: player cash is still kept in the client cloud-save JSON during Phase 2.
create or replace function public.atlas_buy_market_listing(p_listing_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_listing public.atlas_marketplace%rowtype;
    v_buyer uuid := auth.uid();
    v_buyer_name text;
    v_result jsonb;
begin
    if v_buyer is null then
        raise exception 'You must be signed in to buy marketplace parcels.';
    end if;

    select *
    into v_listing
    from public.atlas_marketplace
    where id = p_listing_id
    for update;

    if not found then
        raise exception 'Marketplace listing not found.';
    end if;

    if v_listing.status <> 'active' then
        raise exception 'This parcel listing is no longer active.';
    end if;

    if v_listing.seller_id = v_buyer then
        raise exception 'You cannot buy your own listing.';
    end if;

    select coalesce(display_name, email, 'Atlas Player')
    into v_buyer_name
    from public.atlas_profiles
    where id = v_buyer;

    if v_buyer_name is null then
        v_buyer_name := 'Atlas Player';
    end if;

    update public.atlas_parcels
    set
        owner_id = v_buyer,
        owner_name = v_buyer_name,
        parcel_data = coalesce(parcel_data, '{}'::jsonb)
            || jsonb_build_object(
                'tradedAt', now(),
                'boughtFrom', v_listing.seller_name,
                'marketListingId', v_listing.id
            ),
        updated_at = now()
    where parcel_id = v_listing.parcel_id
    and owner_id = v_listing.seller_id;

    if not found then
        raise exception 'Parcel ownership changed before the trade completed.';
    end if;

    update public.atlas_marketplace
    set
        status = 'sold',
        buyer_id = v_buyer,
        sold_at = now(),
        updated_at = now()
    where id = v_listing.id;

    v_result := jsonb_build_object(
        'listing_id', v_listing.id,
        'parcel_id', v_listing.parcel_id,
        'seller_id', v_listing.seller_id,
        'seller_name', v_listing.seller_name,
        'buyer_id', v_buyer,
        'buyer_name', v_buyer_name,
        'price', v_listing.price,
        'parcel_snapshot', v_listing.parcel_snapshot
    );

    return v_result;
end;
$$;

grant execute on function public.atlas_buy_market_listing(uuid) to authenticated;

-- Optional realtime support. If Supabase says a table is already added, that is fine.
do $$
begin
    begin
        alter publication supabase_realtime add table public.atlas_parcels;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;

    begin
        alter publication supabase_realtime add table public.atlas_leaderboard;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;

    begin
        alter publication supabase_realtime add table public.atlas_marketplace;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;


-- =====================================================
-- Phase 2.4 secure economy: server wallet + ledger.
-- Run this whole file again in Supabase SQL Editor.
-- =====================================================
create table if not exists public.atlas_wallets (
    user_id uuid primary key references auth.users(id) on delete cascade,
    balance numeric not null default 25 check (balance >= 0),
    rent_earned numeric not null default 0,
    rewards_earned numeric not null default 0,
    lifetime_earned numeric not null default 0,
    total_spent numeric not null default 0,
    boost_until timestamptz,
    last_income_claim_at timestamptz not null default now(),
    last_daily_claim_date date,
    daily_streak integer not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_wallets enable row level security;

drop policy if exists "Players can read their own wallet" on public.atlas_wallets;
drop policy if exists "Players can insert their own wallet" on public.atlas_wallets;
drop policy if exists "Players can update their own wallet" on public.atlas_wallets;

create policy "Players can read their own wallet"
on public.atlas_wallets
for select
to authenticated
using (auth.uid() = user_id);

-- Wallet inserts/updates are normally handled by security-definer RPC functions.
-- These policies still let the bootstrap RPC create rows on behalf of the user safely.
create policy "Players can insert their own wallet"
on public.atlas_wallets
for insert
to authenticated
with check (auth.uid() = user_id);

create table if not exists public.atlas_transactions (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users(id) on delete cascade,
    amount numeric not null,
    balance_after numeric not null,
    type text not null default 'info',
    reason text not null default 'Transaction',
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

alter table public.atlas_transactions enable row level security;

create index if not exists atlas_transactions_user_created_idx
on public.atlas_transactions(user_id, created_at desc);

create index if not exists atlas_transactions_type_idx
on public.atlas_transactions(type);

drop policy if exists "Players can read their own transactions" on public.atlas_transactions;

create policy "Players can read their own transactions"
on public.atlas_transactions
for select
to authenticated
using (auth.uid() = user_id);

create or replace function public.atlas_wallet_json(v_wallet public.atlas_wallets)
returns jsonb
language sql
stable
as $$
    select jsonb_build_object(
        'user_id', v_wallet.user_id,
        'balance', v_wallet.balance,
        'rent_earned', v_wallet.rent_earned,
        'rewards_earned', v_wallet.rewards_earned,
        'lifetime_earned', v_wallet.lifetime_earned,
        'total_spent', v_wallet.total_spent,
        'boost_until', v_wallet.boost_until,
        'last_income_claim_at', v_wallet.last_income_claim_at,
        'last_daily_claim_date', v_wallet.last_daily_claim_date,
        'daily_streak', v_wallet.daily_streak,
        'created_at', v_wallet.created_at,
        'updated_at', v_wallet.updated_at
    );
$$;

create or replace function public.atlas_transaction_json(v_tx public.atlas_transactions)
returns jsonb
language sql
stable
as $$
    select case when v_tx is null then null else jsonb_build_object(
        'id', v_tx.id,
        'user_id', v_tx.user_id,
        'amount', v_tx.amount,
        'balance_after', v_tx.balance_after,
        'type', v_tx.type,
        'reason', v_tx.reason,
        'metadata', v_tx.metadata,
        'created_at', v_tx.created_at
    ) end;
$$;

create or replace function public.atlas_ensure_wallet()
returns public.atlas_wallets
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_wallets%rowtype;
begin
    if v_user is null then
        raise exception 'You must be signed in to use the server wallet.';
    end if;

    insert into public.atlas_wallets(user_id)
    values (v_user)
    on conflict (user_id) do nothing;

    select *
    into v_wallet
    from public.atlas_wallets
    where user_id = v_user
    for update;

    return v_wallet;
end;
$$;

create or replace function public.atlas_get_wallet()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_wallet public.atlas_wallets%rowtype;
begin
    v_wallet := public.atlas_ensure_wallet();
    return public.atlas_wallet_json(v_wallet);
end;
$$;

create or replace function public.atlas_claim_income()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_wallets%rowtype;
    v_updated_wallet public.atlas_wallets%rowtype;
    v_tx public.atlas_transactions%rowtype;
    v_base_income numeric := 0;
    v_now timestamptz := now();
    v_elapsed_seconds numeric := 0;
    v_capped_seconds numeric := 0;
    v_start timestamptz;
    v_boost_seconds numeric := 0;
    v_normal_seconds numeric := 0;
    v_earned numeric := 0;
    v_was_capped boolean := false;
begin
    if v_user is null then
        raise exception 'You must be signed in to claim income.';
    end if;

    v_wallet := public.atlas_ensure_wallet();

    select coalesce(sum(income_per_second), 0)
    into v_base_income
    from public.atlas_parcels
    where owner_id = v_user;

    v_elapsed_seconds := greatest(0, extract(epoch from (v_now - v_wallet.last_income_claim_at)));
    v_capped_seconds := least(v_elapsed_seconds, 28800);
    v_was_capped := v_elapsed_seconds > v_capped_seconds;
    v_start := v_now - make_interval(secs => v_capped_seconds);

    if v_wallet.boost_until is not null and v_wallet.boost_until > v_start then
        v_boost_seconds := greatest(0, extract(epoch from (least(v_wallet.boost_until, v_now) - v_start)));
    end if;

    v_boost_seconds := least(v_boost_seconds, v_capped_seconds);
    v_normal_seconds := greatest(0, v_capped_seconds - v_boost_seconds);
    v_earned := round((v_base_income * v_normal_seconds) + (v_base_income * 2 * v_boost_seconds), 8);

    update public.atlas_wallets
    set
        balance = balance + v_earned,
        rent_earned = rent_earned + v_earned,
        lifetime_earned = lifetime_earned + v_earned,
        last_income_claim_at = v_now,
        boost_until = case when boost_until is not null and boost_until <= v_now then null else boost_until end,
        updated_at = v_now
    where user_id = v_user
    returning * into v_updated_wallet;

    if v_earned > 0 then
        insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
        values (
            v_user,
            v_earned,
            v_updated_wallet.balance,
            'income',
            'Rent claimed',
            jsonb_build_object(
                'seconds', v_capped_seconds,
                'base_income_per_second', v_base_income,
                'boost_seconds', v_boost_seconds,
                'was_capped', v_was_capped
            )
        )
        returning * into v_tx;
    end if;

    return jsonb_build_object(
        'earned', v_earned,
        'seconds', v_capped_seconds,
        'was_capped', v_was_capped,
        'wallet', public.atlas_wallet_json(v_updated_wallet),
        'transaction', public.atlas_transaction_json(v_tx)
    );
end;
$$;

create or replace function public.atlas_claim_daily_reward()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_wallets%rowtype;
    v_updated_wallet public.atlas_wallets%rowtype;
    v_tx public.atlas_transactions%rowtype;
    v_today date := current_date;
    v_yesterday date := current_date - 1;
    v_new_streak integer := 1;
    v_parcels integer := 0;
    v_reward numeric := 0;
begin
    if v_user is null then
        raise exception 'You must be signed in to claim daily rewards.';
    end if;

    v_wallet := public.atlas_ensure_wallet();

    if v_wallet.last_daily_claim_date = v_today then
        raise exception 'You already claimed today''s reward.';
    end if;

    if v_wallet.last_daily_claim_date = v_yesterday then
        v_new_streak := v_wallet.daily_streak + 1;
    else
        v_new_streak := 1;
    end if;

    select count(*) into v_parcels from public.atlas_parcels where owner_id = v_user;
    v_reward := round(5 + (least(greatest(v_new_streak - 1, 0), 6) * 1.5) + least(v_parcels * 0.1, 5), 2);

    update public.atlas_wallets
    set
        balance = balance + v_reward,
        rewards_earned = rewards_earned + v_reward,
        lifetime_earned = lifetime_earned + v_reward,
        last_daily_claim_date = v_today,
        daily_streak = v_new_streak,
        updated_at = now()
    where user_id = v_user
    returning * into v_updated_wallet;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_user,
        v_reward,
        v_updated_wallet.balance,
        'reward',
        'Daily reward',
        jsonb_build_object('streak', v_new_streak, 'parcels', v_parcels)
    )
    returning * into v_tx;

    return jsonb_build_object(
        'reward', v_reward,
        'streak', v_new_streak,
        'wallet', public.atlas_wallet_json(v_updated_wallet),
        'transaction', public.atlas_transaction_json(v_tx)
    );
end;
$$;

create or replace function public.atlas_activate_boost()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_wallets%rowtype;
    v_updated_wallet public.atlas_wallets%rowtype;
    v_now timestamptz := now();
    v_current_end timestamptz;
    v_max_end timestamptz := now() + interval '6 hours';
    v_new_end timestamptz;
    v_remaining_ms numeric;
begin
    if v_user is null then
        raise exception 'You must be signed in to activate boost.';
    end if;

    v_wallet := public.atlas_ensure_wallet();
    v_current_end := greatest(v_now, coalesce(v_wallet.boost_until, v_now));

    if coalesce(v_wallet.boost_until, v_now) >= v_max_end - interval '1 second' then
        raise exception 'Boost is already at the 6 hour maximum.';
    end if;

    v_new_end := least(v_max_end, v_current_end + interval '30 minutes');

    update public.atlas_wallets
    set boost_until = v_new_end,
        updated_at = v_now
    where user_id = v_user
    returning * into v_updated_wallet;

    v_remaining_ms := round(extract(epoch from (v_new_end - v_now)) * 1000);

    return jsonb_build_object(
        'boost_until', v_new_end,
        'remaining_ms', v_remaining_ms,
        'wallet', public.atlas_wallet_json(v_updated_wallet)
    );
end;
$$;

create or replace function public.atlas_spend_wallet(p_amount numeric, p_reason text, p_metadata jsonb default '{}'::jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_wallets%rowtype;
    v_tx public.atlas_transactions%rowtype;
    v_amount numeric := round(coalesce(p_amount, 0), 2);
begin
    if v_user is null then
        raise exception 'You must be signed in to spend from the server wallet.';
    end if;

    if v_amount <= 0 then
        raise exception 'Spend amount must be greater than zero.';
    end if;

    perform public.atlas_ensure_wallet();

    update public.atlas_wallets
    set
        balance = balance - v_amount,
        total_spent = total_spent + v_amount,
        updated_at = now()
    where user_id = v_user
    and balance >= v_amount
    returning * into v_wallet;

    if not found then
        raise exception 'Not enough server wallet balance.';
    end if;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (v_user, -v_amount, v_wallet.balance, 'spend', coalesce(p_reason, 'Spend'), coalesce(p_metadata, '{}'::jsonb))
    returning * into v_tx;

    return jsonb_build_object(
        'wallet', public.atlas_wallet_json(v_wallet),
        'transaction', public.atlas_transaction_json(v_tx)
    );
end;
$$;

create or replace function public.atlas_buy_new_parcel(
    p_parcel_id text,
    p_price numeric,
    p_parcel_data jsonb,
    p_income_per_second numeric
)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_name text;
    v_wallet public.atlas_wallets%rowtype;
    v_parcel public.atlas_parcels%rowtype;
    v_tx public.atlas_transactions%rowtype;
    v_price numeric := round(coalesce(p_price, 0), 2);
begin
    if v_user is null then
        raise exception 'You must be signed in to buy global parcels.';
    end if;

    if coalesce(p_parcel_id, '') = '' then
        raise exception 'Missing parcel ID.';
    end if;

    if v_price <= 0 then
        raise exception 'Invalid parcel price.';
    end if;

    perform public.atlas_ensure_wallet();

    if exists (select 1 from public.atlas_parcels where parcel_id = p_parcel_id) then
        raise exception 'This parcel is already owned by another player.';
    end if;

    update public.atlas_wallets
    set
        balance = balance - v_price,
        total_spent = total_spent + v_price,
        updated_at = now()
    where user_id = v_user
    and balance >= v_price
    returning * into v_wallet;

    if not found then
        raise exception 'Not enough server wallet balance.';
    end if;

    select coalesce(display_name, email, 'Atlas Player')
    into v_name
    from public.atlas_profiles
    where id = v_user;

    if v_name is null then
        v_name := 'Atlas Player';
    end if;

    insert into public.atlas_parcels(parcel_id, owner_id, owner_name, parcel_data, income_per_second, updated_at)
    values (
        p_parcel_id,
        v_user,
        v_name,
        coalesce(p_parcel_data, '{}'::jsonb) || jsonb_build_object('price', v_price, 'serverWalletPurchase', true),
        greatest(0, coalesce(p_income_per_second, 0)),
        now()
    )
    returning * into v_parcel;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_user,
        -v_price,
        v_wallet.balance,
        'parcel_buy',
        'Parcel purchase',
        jsonb_build_object('parcel_id', p_parcel_id, 'income_per_second', p_income_per_second)
    )
    returning * into v_tx;

    return jsonb_build_object(
        'wallet', public.atlas_wallet_json(v_wallet),
        'parcel', jsonb_build_object(
            'parcel_id', v_parcel.parcel_id,
            'owner_id', v_parcel.owner_id,
            'owner_name', v_parcel.owner_name,
            'parcel_data', v_parcel.parcel_data,
            'income_per_second', v_parcel.income_per_second,
            'bought_at', v_parcel.bought_at,
            'updated_at', v_parcel.updated_at
        ),
        'transaction', public.atlas_transaction_json(v_tx)
    );
end;
$$;

-- Replaces the Phase 2.3 marketplace transfer with a wallet-safe transfer.
create or replace function public.atlas_buy_market_listing(p_listing_id uuid)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_listing public.atlas_marketplace%rowtype;
    v_buyer uuid := auth.uid();
    v_buyer_name text;
    v_buyer_wallet public.atlas_wallets%rowtype;
    v_seller_wallet public.atlas_wallets%rowtype;
    v_buyer_tx public.atlas_transactions%rowtype;
    v_seller_tx public.atlas_transactions%rowtype;
    v_result jsonb;
begin
    if v_buyer is null then
        raise exception 'You must be signed in to buy marketplace parcels.';
    end if;

    perform public.atlas_ensure_wallet();

    select *
    into v_listing
    from public.atlas_marketplace
    where id = p_listing_id
    for update;

    if not found then
        raise exception 'Marketplace listing not found.';
    end if;

    if v_listing.status <> 'active' then
        raise exception 'This parcel listing is no longer active.';
    end if;

    if v_listing.seller_id = v_buyer then
        raise exception 'You cannot buy your own listing.';
    end if;

    update public.atlas_wallets
    set
        balance = balance - v_listing.price,
        total_spent = total_spent + v_listing.price,
        updated_at = now()
    where user_id = v_buyer
    and balance >= v_listing.price
    returning * into v_buyer_wallet;

    if not found then
        raise exception 'Not enough server wallet balance.';
    end if;

    insert into public.atlas_wallets(user_id)
    values (v_listing.seller_id)
    on conflict (user_id) do nothing;

    update public.atlas_wallets
    set
        balance = balance + v_listing.price,
        lifetime_earned = lifetime_earned + v_listing.price,
        updated_at = now()
    where user_id = v_listing.seller_id
    returning * into v_seller_wallet;

    select coalesce(display_name, email, 'Atlas Player')
    into v_buyer_name
    from public.atlas_profiles
    where id = v_buyer;

    if v_buyer_name is null then
        v_buyer_name := 'Atlas Player';
    end if;

    update public.atlas_parcels
    set
        owner_id = v_buyer,
        owner_name = v_buyer_name,
        parcel_data = coalesce(parcel_data, '{}'::jsonb)
            || jsonb_build_object(
                'tradedAt', now(),
                'boughtFrom', v_listing.seller_name,
                'marketListingId', v_listing.id,
                'serverWalletTrade', true
            ),
        updated_at = now()
    where parcel_id = v_listing.parcel_id
    and owner_id = v_listing.seller_id;

    if not found then
        raise exception 'Parcel ownership changed before the trade completed.';
    end if;

    update public.atlas_marketplace
    set
        status = 'sold',
        buyer_id = v_buyer,
        sold_at = now(),
        updated_at = now()
    where id = v_listing.id;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_buyer,
        -v_listing.price,
        v_buyer_wallet.balance,
        'market_buy',
        'Marketplace parcel purchase',
        jsonb_build_object('listing_id', v_listing.id, 'parcel_id', v_listing.parcel_id, 'seller_id', v_listing.seller_id)
    )
    returning * into v_buyer_tx;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_listing.seller_id,
        v_listing.price,
        v_seller_wallet.balance,
        'market_sale',
        'Marketplace parcel sale',
        jsonb_build_object('listing_id', v_listing.id, 'parcel_id', v_listing.parcel_id, 'buyer_id', v_buyer)
    )
    returning * into v_seller_tx;

    v_result := jsonb_build_object(
        'listing_id', v_listing.id,
        'parcel_id', v_listing.parcel_id,
        'seller_id', v_listing.seller_id,
        'seller_name', v_listing.seller_name,
        'buyer_id', v_buyer,
        'buyer_name', v_buyer_name,
        'price', v_listing.price,
        'parcel_snapshot', v_listing.parcel_snapshot,
        'wallet', public.atlas_wallet_json(v_buyer_wallet),
        'seller_wallet', public.atlas_wallet_json(v_seller_wallet),
        'transaction', public.atlas_transaction_json(v_buyer_tx),
        'seller_transaction', public.atlas_transaction_json(v_seller_tx)
    );

    return v_result;
end;
$$;

grant execute on function public.atlas_wallet_json(public.atlas_wallets) to authenticated;
grant execute on function public.atlas_transaction_json(public.atlas_transactions) to authenticated;
grant execute on function public.atlas_ensure_wallet() to authenticated;
grant execute on function public.atlas_get_wallet() to authenticated;
grant execute on function public.atlas_claim_income() to authenticated;
grant execute on function public.atlas_claim_daily_reward() to authenticated;
grant execute on function public.atlas_activate_boost() to authenticated;
grant execute on function public.atlas_spend_wallet(numeric, text, jsonb) to authenticated;
grant execute on function public.atlas_buy_new_parcel(text, numeric, jsonb, numeric) to authenticated;
grant execute on function public.atlas_buy_market_listing(uuid) to authenticated;

-- Optional realtime support for wallet transaction history.
do $$
begin
    begin
        alter publication supabase_realtime add table public.atlas_transactions;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;

-- =====================================================
-- Phase 2.5 real player profiles.
-- Public profile cards are separate from private cloud saves.
-- =====================================================
create table if not exists public.parcel_player_profiles (
    user_id uuid primary key references auth.users(id) on delete cascade,
    email text,
    username text not null,
    display_name text not null default 'Atlas Player',
    avatar_emoji text not null default '🌍',
    bio text not null default 'Building my virtual empire.',
    home_country text not null default '',
    banner_title text not null default 'Empire Builder',
    is_public boolean not null default true,
    favorite_parcel_id text,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint parcel_player_profiles_username_len check (char_length(username) between 3 and 20),
    constraint parcel_player_profiles_username_format check (username ~ '^[a-z0-9_]+$'),
    constraint parcel_player_profiles_display_name_len check (char_length(display_name) between 1 and 32),
    constraint parcel_player_profiles_bio_len check (char_length(bio) <= 140)
);

alter table public.parcel_player_profiles add column if not exists email text;
alter table public.parcel_player_profiles add column if not exists username text;
alter table public.parcel_player_profiles add column if not exists display_name text not null default 'Atlas Player';
alter table public.parcel_player_profiles add column if not exists avatar_emoji text not null default '🌍';
alter table public.parcel_player_profiles add column if not exists bio text not null default 'Building my virtual empire.';
alter table public.parcel_player_profiles add column if not exists home_country text not null default '';
alter table public.parcel_player_profiles add column if not exists banner_title text not null default 'Empire Builder';
alter table public.parcel_player_profiles add column if not exists is_public boolean not null default true;
alter table public.parcel_player_profiles add column if not exists favorite_parcel_id text;
alter table public.parcel_player_profiles add column if not exists created_at timestamptz not null default now();
alter table public.parcel_player_profiles add column if not exists updated_at timestamptz not null default now();

create unique index if not exists parcel_player_profiles_username_unique_idx
on public.parcel_player_profiles (lower(username));

create index if not exists parcel_player_profiles_display_name_idx
on public.parcel_player_profiles(display_name);

create index if not exists parcel_player_profiles_updated_idx
on public.parcel_player_profiles(updated_at desc);

alter table public.parcel_player_profiles enable row level security;

drop policy if exists "Public can read published player profiles" on public.parcel_player_profiles;
drop policy if exists "Players can insert their own public profile" on public.parcel_player_profiles;
drop policy if exists "Players can update their own public profile" on public.parcel_player_profiles;

create policy "Public can read published player profiles"
on public.parcel_player_profiles
for select
to anon, authenticated
using (is_public = true or auth.uid() = user_id);

create policy "Players can insert their own public profile"
on public.parcel_player_profiles
for insert
to authenticated
with check (auth.uid() = user_id);

create policy "Players can update their own public profile"
on public.parcel_player_profiles
for update
to authenticated
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

-- Seed public profile rows for existing users who already have private atlas_profiles rows.
insert into public.parcel_player_profiles(user_id, email, username, display_name, avatar_emoji, bio, banner_title, is_public)
select
    p.id,
    p.email,
    lower(left(regexp_replace(coalesce(split_part(p.email, '@', 1), 'parcel_player'), '[^a-zA-Z0-9_]', '_', 'g'), 12))
        || '_' || lower(left(replace(p.id::text, '-', ''), 5)),
    coalesce(nullif(p.display_name, ''), p.email, 'Atlas Player'),
    '🌍',
    'Building my virtual empire.',
    'Empire Builder',
    true
from public.atlas_profiles p
where not exists (
    select 1 from public.parcel_player_profiles app where app.user_id = p.id
)
on conflict (user_id) do nothing;

do $$
begin
    begin
        alter publication supabase_realtime add table public.parcel_player_profiles;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;

-- =====================================================
-- Phase 2.6 secure online parcel upgrades.
-- Upgrade costs, levels, income multipliers and ownership checks are now validated by Supabase.
-- =====================================================
create table if not exists public.atlas_upgrade_catalog (
    upgrade_id text not null,
    level integer not null,
    name text not null,
    title text not null,
    cost numeric not null check (cost > 0),
    income_multiplier numeric not null check (income_multiplier > 0),
    emoji text not null default '🏗️',
    active boolean not null default true,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    primary key (upgrade_id, level)
);

alter table public.atlas_upgrade_catalog enable row level security;

drop policy if exists "Public can read upgrade catalog" on public.atlas_upgrade_catalog;
create policy "Public can read upgrade catalog"
on public.atlas_upgrade_catalog
for select
to anon, authenticated
using (true);

insert into public.atlas_upgrade_catalog(upgrade_id, level, name, title, cost, income_multiplier, emoji, active, updated_at)
values
    ('residential', 1, 'Residential', 'Starter House', 8, 1.6, '🏠', true, now()),
    ('residential', 2, 'Residential', 'Town Houses', 18, 2.3, '🏘️', true, now()),
    ('residential', 3, 'Residential', 'Luxury Apartments', 42, 3.4, '🏢', true, now()),
    ('commercial', 1, 'Commercial', 'Corner Shop', 14, 2.1, '🏪', true, now()),
    ('commercial', 2, 'Commercial', 'Retail Row', 34, 3.2, '🏬', true, now()),
    ('commercial', 3, 'Commercial', 'Shopping Complex', 78, 5.0, '🏬', true, now()),
    ('tower', 1, 'Tower', 'Office Block', 32, 3.1, '🏢', true, now()),
    ('tower', 2, 'Tower', 'City Tower', 86, 5.8, '🏙️', true, now()),
    ('tower', 3, 'Tower', 'Skyline Icon', 190, 9.5, '🌆', true, now()),
    ('landmark', 1, 'Landmark', 'Local Landmark', 65, 5.2, '🏛️', true, now()),
    ('landmark', 2, 'Landmark', 'Tourist Hotspot', 150, 9.0, '🎡', true, now()),
    ('landmark', 3, 'Landmark', 'World Wonder', 350, 16.0, '🗽', true, now())
on conflict (upgrade_id, level) do update set
    name = excluded.name,
    title = excluded.title,
    cost = excluded.cost,
    income_multiplier = excluded.income_multiplier,
    emoji = excluded.emoji,
    active = excluded.active,
    updated_at = now();

create or replace function public.atlas_upgrade_parcel(
    p_parcel_id text,
    p_upgrade_id text
)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_parcel public.atlas_parcels%rowtype;
    v_catalog public.atlas_upgrade_catalog%rowtype;
    v_wallet public.atlas_wallets%rowtype;
    v_tx public.atlas_transactions%rowtype;
    v_data jsonb;
    v_current_upgrade text;
    v_current_level integer := 0;
    v_next_level integer := 1;
    v_base_income numeric := 0;
    v_new_income numeric := 0;
begin
    if v_user is null then
        raise exception 'You must be signed in to upgrade parcels.';
    end if;

    if coalesce(p_parcel_id, '') = '' then
        raise exception 'Missing parcel ID.';
    end if;

    if coalesce(p_upgrade_id, '') = '' then
        raise exception 'Missing upgrade ID.';
    end if;

    perform public.atlas_ensure_wallet();

    select *
    into v_parcel
    from public.atlas_parcels
    where parcel_id = p_parcel_id
    for update;

    if not found then
        raise exception 'Parcel not found.';
    end if;

    if v_parcel.owner_id is distinct from v_user then
        raise exception 'You can only upgrade parcels you own.';
    end if;

    if exists (
        select 1
        from public.atlas_marketplace m
        where m.parcel_id = p_parcel_id
        and m.status = 'active'
    ) then
        raise exception 'Cancel the active marketplace listing before upgrading this parcel.';
    end if;

    v_data := coalesce(v_parcel.parcel_data, '{}'::jsonb);
    v_current_upgrade := coalesce(v_data->>'upgradeId', '');

    if v_current_upgrade = p_upgrade_id then
        v_current_level := coalesce(nullif(v_data->>'upgradeLevel', '')::integer, 0);
    else
        -- Switching building path is allowed, but it restarts at level 1.
        v_current_level := 0;
    end if;

    v_next_level := v_current_level + 1;

    select *
    into v_catalog
    from public.atlas_upgrade_catalog
    where upgrade_id = p_upgrade_id
    and level = v_next_level
    and active = true;

    if not found then
        raise exception 'That upgrade path is already max level or does not exist.';
    end if;

    v_base_income := coalesce(
        nullif(v_data->>'baseIncomePerSecond', '')::numeric,
        nullif(v_data->>'originalIncomePerSecond', '')::numeric,
        nullif(v_data->>'base_income_per_second', '')::numeric,
        v_parcel.income_per_second,
        0
    );

    if v_base_income <= 0 then
        raise exception 'Parcel base income is invalid.';
    end if;

    v_new_income := round(v_base_income * v_catalog.income_multiplier, 12);

    update public.atlas_wallets
    set
        balance = balance - v_catalog.cost,
        total_spent = total_spent + v_catalog.cost,
        updated_at = now()
    where user_id = v_user
    and balance >= v_catalog.cost
    returning * into v_wallet;

    if not found then
        raise exception 'Not enough server wallet balance for this upgrade.';
    end if;

    v_data := v_data || jsonb_build_object(
        'baseIncomePerSecond', v_base_income,
        'incomePerSecond', v_new_income,
        'upgradeId', v_catalog.upgrade_id,
        'upgradeLevel', v_catalog.level,
        'upgradeName', v_catalog.name,
        'upgradeEmoji', v_catalog.emoji,
        'upgradeTitle', v_catalog.title,
        'upgradeCost', v_catalog.cost,
        'upgradeMultiplier', v_catalog.income_multiplier,
        'secureUpgrade', true,
        'upgradedAt', now()
    );

    update public.atlas_parcels
    set
        parcel_data = v_data,
        income_per_second = v_new_income,
        updated_at = now()
    where parcel_id = p_parcel_id
    and owner_id = v_user
    returning * into v_parcel;

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_user,
        -v_catalog.cost,
        v_wallet.balance,
        'parcel_upgrade',
        'Parcel upgrade',
        jsonb_build_object(
            'parcel_id', p_parcel_id,
            'upgrade_id', v_catalog.upgrade_id,
            'level', v_catalog.level,
            'title', v_catalog.title,
            'income_per_second', v_new_income
        )
    )
    returning * into v_tx;

    return jsonb_build_object(
        'wallet', public.atlas_wallet_json(v_wallet),
        'transaction', public.atlas_transaction_json(v_tx),
        'parcel', jsonb_build_object(
            'parcel_id', v_parcel.parcel_id,
            'owner_id', v_parcel.owner_id,
            'owner_name', v_parcel.owner_name,
            'parcel_data', v_parcel.parcel_data,
            'income_per_second', v_parcel.income_per_second,
            'bought_at', v_parcel.bought_at,
            'updated_at', v_parcel.updated_at
        ),
        'upgrade', jsonb_build_object(
            'upgrade_id', v_catalog.upgrade_id,
            'name', v_catalog.name,
            'title', v_catalog.title,
            'level', v_catalog.level,
            'cost', v_catalog.cost,
            'income_multiplier', v_catalog.income_multiplier,
            'emoji', v_catalog.emoji,
            'income_per_second', v_new_income
        )
    );
end;
$$;

grant execute on function public.atlas_upgrade_parcel(text, text) to authenticated;

-- Optional realtime support for central upgrade-catalog changes.
do $$
begin
    begin
        alter publication supabase_realtime add table public.atlas_upgrade_catalog;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;


-- =====================================================
-- PHASE 2.8 - EVENTS, NOTIFICATIONS AND REAL-MONEY-READY REWARDS
-- =====================================================

create table if not exists public.atlas_app_settings (
    key text primary key,
    value jsonb not null default '{}'::jsonb,
    updated_at timestamptz not null default now()
);

insert into public.atlas_app_settings(key, value)
values (
    'real_money',
    jsonb_build_object(
        'enabled', false,
        'currency', 'GBP',
        'min_cashout', 5,
        'notice', 'Payouts are disabled until legal, tax, KYC/age, anti-fraud and payout-provider setup is complete.'
    )
)
on conflict (key) do nothing;

create table if not exists public.atlas_reward_wallets (
    user_id uuid primary key references auth.users(id) on delete cascade,
    redeemable_balance numeric(12,4) not null default 0,
    pending_payouts numeric(12,4) not null default 0,
    total_earned numeric(12,4) not null default 0,
    total_paid numeric(12,4) not null default 0,
    currency text not null default 'GBP',
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now(),
    constraint atlas_reward_wallet_nonnegative check (redeemable_balance >= 0 and pending_payouts >= 0 and total_earned >= 0 and total_paid >= 0)
);

create table if not exists public.atlas_payout_requests (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users(id) on delete cascade,
    amount numeric(12,4) not null check (amount > 0),
    currency text not null default 'GBP',
    status text not null default 'pending' check (status in ('pending','approved','rejected','paid','cancelled')),
    payout_method text not null default 'paypal',
    payout_target_masked text,
    payout_target_private text,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists public.atlas_live_events (
    id text primary key,
    title text not null,
    description text not null,
    metric text not null,
    target numeric(18,8) not null default 1,
    reward_type text not null default 'game_cash' check (reward_type in ('game_cash','redeemable')),
    reward_amount numeric(12,4) not null default 0,
    reward_label text,
    repeat_key text,
    starts_at timestamptz,
    ends_at timestamptz,
    active boolean not null default true,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

create table if not exists public.atlas_event_claims (
    user_id uuid not null references auth.users(id) on delete cascade,
    event_id text not null references public.atlas_live_events(id) on delete cascade,
    reward_type text not null,
    reward_amount numeric(12,4) not null default 0,
    metric_value numeric(18,8) not null default 0,
    claimed_at timestamptz not null default now(),
    primary key (user_id, event_id)
);

insert into public.atlas_live_events(id, title, description, metric, target, reward_type, reward_amount, reward_label, active, metadata)
values
('daily_login', 'Daily Check-in', 'Open the world map and claim today’s reward.', 'daily_login', 1, 'game_cash', 0.50, '£0.50 game cash', true, '{}'::jsonb),
('first_parcel', 'Buy Your First Parcel', 'Own at least one parcel in the global world.', 'parcels_owned', 1, 'redeemable', 0.01, '£0.01 redeemable', true, '{}'::jsonb),
('first_upgrade', 'Build Something', 'Upgrade one of your owned parcels.', 'upgrades_owned', 1, 'game_cash', 1.50, '£1.50 game cash', true, '{}'::jsonb),
('income_builder', 'Income Builder', 'Reach £0.000020/sec global income.', 'income_per_second', 0.000020, 'redeemable', 0.02, '£0.02 redeemable', true, '{}'::jsonb)
on conflict (id) do update set
    title = excluded.title,
    description = excluded.description,
    metric = excluded.metric,
    target = excluded.target,
    reward_type = excluded.reward_type,
    reward_amount = excluded.reward_amount,
    reward_label = excluded.reward_label,
    active = excluded.active,
    updated_at = now();

alter table public.atlas_app_settings enable row level security;
alter table public.atlas_reward_wallets enable row level security;
alter table public.atlas_payout_requests enable row level security;
alter table public.atlas_live_events enable row level security;
alter table public.atlas_event_claims enable row level security;

drop policy if exists atlas_app_settings_read on public.atlas_app_settings;
create policy atlas_app_settings_read on public.atlas_app_settings for select using (true);

drop policy if exists atlas_reward_wallet_owner_read on public.atlas_reward_wallets;
create policy atlas_reward_wallet_owner_read on public.atlas_reward_wallets for select using (auth.uid() = user_id);

drop policy if exists atlas_payout_owner_read on public.atlas_payout_requests;
create policy atlas_payout_owner_read on public.atlas_payout_requests for select using (auth.uid() = user_id);

drop policy if exists atlas_live_events_read on public.atlas_live_events;
create policy atlas_live_events_read on public.atlas_live_events for select using (active = true);

drop policy if exists atlas_event_claims_owner_read on public.atlas_event_claims;
create policy atlas_event_claims_owner_read on public.atlas_event_claims for select using (auth.uid() = user_id);

create or replace function public.atlas_reward_wallet_json(p_wallet public.atlas_reward_wallets)
returns jsonb
language sql
stable
as $$
    select jsonb_build_object(
        'user_id', p_wallet.user_id,
        'redeemable_balance', p_wallet.redeemable_balance,
        'pending_payouts', p_wallet.pending_payouts,
        'total_earned', p_wallet.total_earned,
        'total_paid', p_wallet.total_paid,
        'currency', p_wallet.currency,
        'created_at', p_wallet.created_at,
        'updated_at', p_wallet.updated_at
    );
$$;

create or replace function public.atlas_payout_request_json(p_request public.atlas_payout_requests)
returns jsonb
language sql
stable
as $$
    select jsonb_build_object(
        'id', p_request.id,
        'user_id', p_request.user_id,
        'amount', p_request.amount,
        'currency', p_request.currency,
        'status', p_request.status,
        'payout_method', p_request.payout_method,
        'payout_target_masked', p_request.payout_target_masked,
        'metadata', p_request.metadata,
        'created_at', p_request.created_at,
        'updated_at', p_request.updated_at
    );
$$;

create or replace function public.atlas_get_reward_wallet()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_reward_wallets;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    insert into public.atlas_reward_wallets(user_id)
    values (v_user)
    on conflict (user_id) do nothing;

    select * into v_wallet
    from public.atlas_reward_wallets
    where user_id = v_user;

    return public.atlas_reward_wallet_json(v_wallet);
end;
$$;

grant execute on function public.atlas_get_reward_wallet() to authenticated;

create or replace function public.atlas_event_metric_value(p_metric text)
returns numeric
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_value numeric := 0;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    if p_metric = 'daily_login' then
        return 1;
    elsif p_metric = 'parcels_owned' then
        select count(*)::numeric into v_value from public.atlas_parcels where owner_id = v_user;
    elsif p_metric = 'upgrades_owned' then
        select count(*)::numeric into v_value
        from public.atlas_parcels
        where owner_id = v_user
        and coalesce(nullif(parcel_data->>'upgradeLevel','')::numeric, 0) > 0;
    elsif p_metric = 'income_per_second' then
        select coalesce(sum(income_per_second), 0)::numeric into v_value from public.atlas_parcels where owner_id = v_user;
    else
        v_value := 0;
    end if;

    return coalesce(v_value, 0);
end;
$$;

grant execute on function public.atlas_event_metric_value(text) to authenticated;

create or replace function public.atlas_claim_event_reward(p_event_id text, p_metric_value numeric default 0)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_event public.atlas_live_events;
    v_metric_value numeric := 0;
    v_wallet public.atlas_wallets;
    v_reward_wallet public.atlas_reward_wallets;
    v_tx public.atlas_transactions;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    select * into v_event
    from public.atlas_live_events
    where id = p_event_id
    and active = true
    and (starts_at is null or starts_at <= now())
    and (ends_at is null or ends_at >= now());

    if not found then
        raise exception 'That event is not active.';
    end if;

    if exists (select 1 from public.atlas_event_claims where user_id = v_user and event_id = p_event_id) then
        raise exception 'You already claimed this event.';
    end if;

    v_metric_value := greatest(public.atlas_event_metric_value(v_event.metric), coalesce(p_metric_value, 0));

    if v_metric_value < v_event.target then
        raise exception 'Event objective is not complete yet.';
    end if;

    if v_event.reward_type = 'redeemable' then
        insert into public.atlas_reward_wallets(user_id)
        values (v_user)
        on conflict (user_id) do nothing;

        update public.atlas_reward_wallets
        set
            redeemable_balance = redeemable_balance + v_event.reward_amount,
            total_earned = total_earned + v_event.reward_amount,
            updated_at = now()
        where user_id = v_user
        returning * into v_reward_wallet;
    else
        insert into public.atlas_wallets(user_id, balance)
        values (v_user, 0)
        on conflict (user_id) do nothing;

        update public.atlas_wallets
        set
            balance = balance + v_event.reward_amount,
            rewards_earned = rewards_earned + v_event.reward_amount,
            lifetime_earned = lifetime_earned + v_event.reward_amount,
            updated_at = now()
        where user_id = v_user
        returning * into v_wallet;

        insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
        values (
            v_user,
            v_event.reward_amount,
            v_wallet.balance,
            'event_reward',
            'Event reward',
            jsonb_build_object('event_id', v_event.id, 'event_title', v_event.title, 'reward_type', v_event.reward_type)
        )
        returning * into v_tx;
    end if;

    insert into public.atlas_event_claims(user_id, event_id, reward_type, reward_amount, metric_value)
    values (v_user, v_event.id, v_event.reward_type, v_event.reward_amount, v_metric_value);

    return jsonb_build_object(
        'event_id', v_event.id,
        'reward_type', v_event.reward_type,
        'reward_amount', v_event.reward_amount,
        'metric_value', v_metric_value,
        'wallet', case when v_wallet.user_id is null then null else public.atlas_wallet_json(v_wallet) end,
        'reward_wallet', case when v_reward_wallet.user_id is null then null else public.atlas_reward_wallet_json(v_reward_wallet) end,
        'transaction', case when v_tx.id is null then null else public.atlas_transaction_json(v_tx) end
    );
end;
$$;

grant execute on function public.atlas_claim_event_reward(text, numeric) to authenticated;

create or replace function public.atlas_request_payout(p_amount numeric, p_payout_method text, p_payout_target text)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_wallet public.atlas_reward_wallets;
    v_request public.atlas_payout_requests;
    v_enabled boolean := false;
    v_min_cashout numeric := 5;
    v_currency text := 'GBP';
    v_target text := trim(coalesce(p_payout_target, ''));
    v_masked text;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    select
        coalesce((value->>'enabled')::boolean, false),
        coalesce((value->>'min_cashout')::numeric, 5),
        coalesce(value->>'currency', 'GBP')
    into v_enabled, v_min_cashout, v_currency
    from public.atlas_app_settings
    where key = 'real_money';

    if not coalesce(v_enabled, false) then
        raise exception 'Real-money payouts are disabled until compliance and payout processing are ready.';
    end if;

    if p_amount < v_min_cashout then
        raise exception 'Minimum cashout is % %.', v_currency, v_min_cashout;
    end if;

    if length(v_target) < 4 then
        raise exception 'Payout target is required.';
    end if;

    v_masked := case
        when position('@' in v_target) > 1 then left(v_target, 2) || '***@' || split_part(v_target, '@', 2)
        else left(v_target, 2) || '***' || right(v_target, 2)
    end;

    insert into public.atlas_reward_wallets(user_id, currency)
    values (v_user, v_currency)
    on conflict (user_id) do nothing;

    update public.atlas_reward_wallets
    set
        redeemable_balance = redeemable_balance - p_amount,
        pending_payouts = pending_payouts + p_amount,
        currency = v_currency,
        updated_at = now()
    where user_id = v_user
    and redeemable_balance >= p_amount
    returning * into v_wallet;

    if not found then
        raise exception 'Not enough redeemable balance.';
    end if;

    insert into public.atlas_payout_requests(user_id, amount, currency, status, payout_method, payout_target_masked, payout_target_private, metadata)
    values (v_user, p_amount, v_currency, 'pending', left(coalesce(p_payout_method, 'paypal'), 40), v_masked, v_target, jsonb_build_object('source','atlas_v2_phase_2_8'))
    returning * into v_request;

    return jsonb_build_object(
        'request', public.atlas_payout_request_json(v_request),
        'reward_wallet', public.atlas_reward_wallet_json(v_wallet)
    );
end;
$$;

grant execute on function public.atlas_request_payout(numeric, text, text) to authenticated;

-- Enable realtime for live event changes if available.
do $$
begin
    begin
        alter publication supabase_realtime add table public.atlas_live_events;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;


-- =====================================================
-- PHASE 2.9 ADS, REVENUE ENGINE AND REWARD RULES
-- =====================================================

insert into public.atlas_app_settings(key, value)
values (
    'reward_rules',
    jsonb_build_object(
        'ads_enabled', true,
        'rewarded_ads_enabled', true,
        'provider', 'demo_simulator',
        'daily_limit', 20,
        'cooldown_seconds', 60,
        'estimated_ecpm', 5.00,
        'reward_share', 0.40,
        'max_reward_per_ad', 0.01,
        'max_daily_redeemable_reward', 0.10,
        'min_account_age_days', 0,
        'terms_version', 'phase2.9-prototype',
        'require_terms', true,
        'real_money_enabled', false
    )
)
on conflict (key) do update set
    value = public.atlas_app_settings.value || excluded.value,
    updated_at = now();

create table if not exists public.atlas_reward_terms_acceptances (
    user_id uuid not null references auth.users(id) on delete cascade,
    terms_version text not null,
    accepted_at timestamptz not null default now(),
    ip_hint text,
    user_agent_hint text,
    primary key (user_id, terms_version)
);

alter table public.atlas_reward_terms_acceptances enable row level security;

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

create table if not exists public.atlas_ad_views (
    id uuid primary key default gen_random_uuid(),
    user_id uuid not null references auth.users(id) on delete cascade,
    provider text not null default 'demo_simulator',
    placement text not null default 'rewarded',
    estimated_revenue numeric not null default 0,
    reward_amount numeric not null default 0,
    reward_share numeric not null default 0,
    status text not null default 'credited',
    client_session text,
    device_hash text,
    risk_score integer not null default 0,
    risk_flags jsonb not null default '[]'::jsonb,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now()
);

alter table public.atlas_ad_views enable row level security;

create index if not exists atlas_ad_views_user_created_idx on public.atlas_ad_views(user_id, created_at desc);
create index if not exists atlas_ad_views_created_idx on public.atlas_ad_views(created_at desc);

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

create table if not exists public.atlas_revenue_daily (
    day date primary key,
    ad_views integer not null default 0,
    gross_revenue numeric not null default 0,
    reward_liability numeric not null default 0,
    payout_reserve numeric not null default 0,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_revenue_daily enable row level security;

drop policy if exists atlas_revenue_daily_read on public.atlas_revenue_daily;
create policy atlas_revenue_daily_read
on public.atlas_revenue_daily
for select
to authenticated
using (true);

create or replace function public.atlas_get_reward_rules()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_rules jsonb;
    v_terms_version text;
    v_terms_accepted_at timestamptz;
    v_ads_today integer := 0;
    v_last_ad_at timestamptz;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    select value into v_rules from public.atlas_app_settings where key = 'reward_rules';
    v_rules := coalesce(v_rules, '{}'::jsonb);
    v_terms_version := coalesce(v_rules->>'terms_version', 'phase2.9-prototype');

    select accepted_at into v_terms_accepted_at
    from public.atlas_reward_terms_acceptances
    where user_id = v_user and terms_version = v_terms_version;

    select count(*), max(created_at)
    into v_ads_today, v_last_ad_at
    from public.atlas_ad_views
    where user_id = v_user and created_at::date = current_date;

    return v_rules || jsonb_build_object(
        'terms_accepted_at', v_terms_accepted_at,
        'ads_today', coalesce(v_ads_today, 0),
        'last_ad_at', v_last_ad_at
    );
end;
$$;

grant execute on function public.atlas_get_reward_rules() to authenticated;

create or replace function public.atlas_accept_reward_terms(p_terms_version text default 'phase2.9-prototype')
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_version text := left(trim(coalesce(p_terms_version, 'phase2.9-prototype')), 80);
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    insert into public.atlas_reward_terms_acceptances(user_id, terms_version, accepted_at)
    values (v_user, v_version, now())
    on conflict (user_id, terms_version) do update set accepted_at = excluded.accepted_at;

    return public.atlas_get_reward_rules();
end;
$$;

grant execute on function public.atlas_accept_reward_terms(text) to authenticated;

create or replace function public.atlas_get_revenue_metrics()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_ads_today integer := 0;
    v_total_ads integer := 0;
    v_estimated_revenue numeric := 0;
    v_reward_reserve numeric := 0;
    v_last_reward numeric := 0;
    v_last_ad_at timestamptz;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    select count(*), coalesce(sum(estimated_revenue),0), coalesce(sum(reward_amount),0), max(created_at)
    into v_ads_today, v_estimated_revenue, v_reward_reserve, v_last_ad_at
    from public.atlas_ad_views
    where user_id = v_user and created_at::date = current_date;

    select count(*) into v_total_ads
    from public.atlas_ad_views
    where user_id = v_user;

    select reward_amount into v_last_reward
    from public.atlas_ad_views
    where user_id = v_user
    order by created_at desc
    limit 1;

    return jsonb_build_object(
        'day', current_date,
        'ads_today', coalesce(v_ads_today,0),
        'total_ads', coalesce(v_total_ads,0),
        'estimated_gross_revenue', coalesce(v_estimated_revenue,0),
        'reward_reserve', coalesce(v_reward_reserve,0),
        'last_reward', coalesce(v_last_reward,0),
        'last_ad_at', v_last_ad_at
    );
end;
$$;

grant execute on function public.atlas_get_revenue_metrics() to authenticated;

create or replace function public.atlas_record_rewarded_ad(
    p_provider text default 'demo_simulator',
    p_placement text default 'rewarded',
    p_client_session text default '',
    p_device_hash text default ''
)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_rules jsonb;
    v_terms_version text;
    v_terms_accepted_at timestamptz;
    v_daily_limit integer;
    v_cooldown_seconds integer;
    v_ads_today integer := 0;
    v_last_ad_at timestamptz;
    v_ecpm numeric;
    v_reward_share numeric;
    v_max_reward numeric;
    v_daily_max numeric;
    v_daily_reward_so_far numeric := 0;
    v_estimated_revenue numeric;
    v_reward_amount numeric;
    v_reward_wallet public.atlas_reward_wallets%rowtype;
    v_ad public.atlas_ad_views%rowtype;
    v_metrics jsonb;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    select value into v_rules from public.atlas_app_settings where key = 'reward_rules';
    v_rules := coalesce(v_rules, '{}'::jsonb);

    if not coalesce((v_rules->>'ads_enabled')::boolean, true) or not coalesce((v_rules->>'rewarded_ads_enabled')::boolean, true) then
        raise exception 'Rewarded ads are disabled.';
    end if;

    v_terms_version := coalesce(v_rules->>'terms_version', 'phase2.9-prototype');
    if coalesce((v_rules->>'require_terms')::boolean, true) then
        select accepted_at into v_terms_accepted_at
        from public.atlas_reward_terms_acceptances
        where user_id = v_user and terms_version = v_terms_version;

        if v_terms_accepted_at is null then
            raise exception 'Reward terms must be accepted before earning redeemable rewards.';
        end if;
    end if;

    v_daily_limit := coalesce((v_rules->>'daily_limit')::integer, 20);
    v_cooldown_seconds := coalesce((v_rules->>'cooldown_seconds')::integer, 60);
    v_ecpm := coalesce((v_rules->>'estimated_ecpm')::numeric, 5.00);
    v_reward_share := coalesce((v_rules->>'reward_share')::numeric, 0.40);
    v_max_reward := coalesce((v_rules->>'max_reward_per_ad')::numeric, 0.01);
    v_daily_max := coalesce((v_rules->>'max_daily_redeemable_reward')::numeric, 0.10);

    select count(*), max(created_at), coalesce(sum(reward_amount),0)
    into v_ads_today, v_last_ad_at, v_daily_reward_so_far
    from public.atlas_ad_views
    where user_id = v_user and created_at::date = current_date;

    if v_ads_today >= v_daily_limit then
        raise exception 'Daily rewarded ad limit reached.';
    end if;

    if v_last_ad_at is not null and v_last_ad_at > now() - make_interval(secs => v_cooldown_seconds) then
        raise exception 'Rewarded ad cooldown is still active.';
    end if;

    v_estimated_revenue := greatest(0, v_ecpm / 1000.0);
    v_reward_amount := least(v_max_reward, v_estimated_revenue * v_reward_share, greatest(0, v_daily_max - v_daily_reward_so_far));

    insert into public.atlas_reward_wallets(user_id, currency)
    values (v_user, coalesce((select value->>'currency' from public.atlas_app_settings where key = 'real_money'), 'GBP'))
    on conflict (user_id) do nothing;

    update public.atlas_reward_wallets
    set
        redeemable_balance = redeemable_balance + v_reward_amount,
        total_earned = total_earned + v_reward_amount,
        updated_at = now()
    where user_id = v_user
    returning * into v_reward_wallet;

    insert into public.atlas_ad_views(
        user_id, provider, placement, estimated_revenue, reward_amount, reward_share,
        client_session, device_hash, metadata
    )
    values (
        v_user,
        left(coalesce(nullif(p_provider,''), coalesce(v_rules->>'provider', 'demo_simulator')), 60),
        left(coalesce(nullif(p_placement,''), 'rewarded'), 60),
        v_estimated_revenue,
        v_reward_amount,
        v_reward_share,
        left(coalesce(p_client_session,''), 80),
        left(coalesce(p_device_hash,''), 120),
        jsonb_build_object('rules_version', v_terms_version, 'source', 'phase_2_9')
    )
    returning * into v_ad;

    insert into public.atlas_revenue_daily(day, ad_views, gross_revenue, reward_liability, payout_reserve, updated_at)
    values (current_date, 1, v_estimated_revenue, v_reward_amount, v_reward_amount, now())
    on conflict (day) do update set
        ad_views = public.atlas_revenue_daily.ad_views + 1,
        gross_revenue = public.atlas_revenue_daily.gross_revenue + excluded.gross_revenue,
        reward_liability = public.atlas_revenue_daily.reward_liability + excluded.reward_liability,
        payout_reserve = public.atlas_revenue_daily.payout_reserve + excluded.payout_reserve,
        updated_at = now();

    insert into public.atlas_transactions(user_id, amount, balance_after, type, reason, metadata)
    values (
        v_user,
        v_reward_amount,
        v_reward_wallet.redeemable_balance,
        'rewarded_ad',
        'Rewarded ad redeemable credit',
        jsonb_build_object('ad_view_id', v_ad.id, 'estimated_revenue', v_estimated_revenue, 'reward_share', v_reward_share)
    );

    v_metrics := public.atlas_get_revenue_metrics();

    return jsonb_build_object(
        'ad_view_id', v_ad.id,
        'reward_amount', v_reward_amount,
        'estimated_revenue', v_estimated_revenue,
        'reward_wallet', public.atlas_reward_wallet_json(v_reward_wallet),
        'metrics', v_metrics,
        'ads_today', v_metrics->>'ads_today',
        'total_ads', v_metrics->>'total_ads',
        'estimated_revenue_total', v_metrics->>'estimated_gross_revenue',
        'reward_reserve', v_metrics->>'reward_reserve',
        'created_at', v_ad.created_at
    );
end;
$$;

grant execute on function public.atlas_record_rewarded_ad(text, text, text, text) to authenticated;

-- Enable realtime for ad/revenue tables if available.
do $$
begin
    begin
        alter publication supabase_realtime add table public.atlas_ad_views;
    exception
        when duplicate_object then null;
        when undefined_object then null;
    end;
end $$;

-- =====================================================
-- PHASE 3.0 - ADMIN CONTROL PANEL
-- =====================================================

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

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

create index if not exists atlas_admin_audit_created_idx on public.atlas_admin_audit(created_at desc);
create index if not exists atlas_admin_audit_admin_idx on public.atlas_admin_audit(admin_id, created_at desc);

alter table public.atlas_admins enable row level security;
alter table public.atlas_admin_audit enable row level security;

drop policy if exists atlas_admins_self_read on public.atlas_admins;
create policy atlas_admins_self_read on public.atlas_admins for select using (auth.uid() = user_id);

drop policy if exists atlas_admin_audit_no_direct_read on public.atlas_admin_audit;
create policy atlas_admin_audit_no_direct_read on public.atlas_admin_audit for select using (false);

-- Marketplace moderation can now flag listings without deleting them.
do $$
begin
    begin
        alter table public.atlas_marketplace drop constraint if exists atlas_marketplace_status_check;
    exception when undefined_table then null;
    end;
    begin
        alter table public.atlas_marketplace add constraint atlas_marketplace_status_check check (status in ('active','sold','cancelled','flagged'));
    exception when duplicate_object then null;
    end;
end $$;

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

grant execute on function public.atlas_is_admin() to authenticated;

create or replace function public.atlas_require_admin()
returns uuid
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid := auth.uid();
begin
    if v_admin is null then
        raise exception 'Not authenticated.';
    end if;

    if not exists (select 1 from public.atlas_admins where user_id = v_admin and is_active = true) then
        raise exception 'Admin access required.';
    end if;

    return v_admin;
end;
$$;

create or replace function public.atlas_admin_write_audit(p_action text, p_target_type text default null, p_target_id text default null, p_details jsonb default '{}'::jsonb)
returns void
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
begin
    v_admin := public.atlas_require_admin();

    insert into public.atlas_admin_audit(admin_id, action, target_type, target_id, details)
    values (v_admin, left(coalesce(p_action,'unknown'), 120), left(coalesce(p_target_type,''), 80), left(coalesce(p_target_id,''), 180), coalesce(p_details, '{}'::jsonb));
end;
$$;

create or replace function public.atlas_admin_get_dashboard()
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_result jsonb;
    v_reward_rules jsonb;
    v_real_money jsonb;
begin
    v_admin := public.atlas_require_admin();

    select value into v_reward_rules from public.atlas_app_settings where key = 'reward_rules';
    select value into v_real_money from public.atlas_app_settings where key = 'real_money';

    select jsonb_build_object(
        'mode', 'supabase',
        'generated_at', now(),
        'admin_id', v_admin,
        'reward_rules', coalesce(v_reward_rules, '{}'::jsonb),
        'real_money', coalesce(v_real_money, '{}'::jsonb),
        'totals', jsonb_build_object(
            'players', (select count(*) from public.parcel_player_profiles),
            'profiles', (select count(*) from public.parcel_player_profiles),
            'parcels', (select count(*) from public.atlas_parcels),
            'active_market_listings', (select count(*) from public.atlas_marketplace where status = 'active'),
            'pending_payouts', (select count(*) from public.atlas_payout_requests where status in ('pending','approved')),
            'pending_payout_amount', (select coalesce(sum(amount),0) from public.atlas_payout_requests where status in ('pending','approved')),
            'server_wallet_balance', (select coalesce(sum(balance),0) from public.atlas_wallets),
            'redeemable_liability', (select coalesce(sum(redeemable_balance + pending_payouts),0) from public.atlas_reward_wallets),
            'ad_views_today', (select count(*) from public.atlas_ad_views where created_at::date = current_date),
            'estimated_gross_revenue', (select coalesce(sum(gross_revenue),0) from public.atlas_revenue_daily),
            'reward_reserve', (select coalesce(sum(payout_reserve),0) from public.atlas_revenue_daily),
            'suspicious_ad_users_today', (select count(*) from (select user_id from public.atlas_ad_views where created_at::date = current_date group by user_id having count(*) >= 15) s)
        ),
        'recent_transactions', (
            select coalesce(jsonb_agg(to_jsonb(t)), '[]'::jsonb)
            from (
                select id::text, user_id::text, amount, balance_after, type, reason, metadata, created_at
                from public.atlas_transactions
                order by created_at desc
                limit 12
            ) t
        ),
        'fraud_watch', jsonb_build_array(
            jsonb_build_object('label','High ad-volume users today','value',(select count(*)::text from (select user_id from public.atlas_ad_views where created_at::date = current_date group by user_id having count(*) >= 15) s),'severity',case when (select count(*) from (select user_id from public.atlas_ad_views where created_at::date = current_date group by user_id having count(*) >= 15) s) > 0 then 'warn' else 'good' end,'description','Players near the daily ad cap.'),
            jsonb_build_object('label','Pending payout requests','value',(select count(*)::text from public.atlas_payout_requests where status in ('pending','approved')),'severity',case when (select count(*) from public.atlas_payout_requests where status in ('pending','approved')) > 0 then 'danger' else 'good' end,'description','Requests that need manual review.'),
            jsonb_build_object('label','Flagged marketplace listings','value',(select count(*)::text from public.atlas_marketplace where status = 'flagged'),'severity',case when (select count(*) from public.atlas_marketplace where status = 'flagged') > 0 then 'warn' else 'good' end,'description','Listings held for moderation.')
        ),
        'recent_audit', (
            select coalesce(jsonb_agg(to_jsonb(a)), '[]'::jsonb)
            from (
                select id, admin_id::text, action, target_type, target_id, details, created_at
                from public.atlas_admin_audit
                order by created_at desc
                limit 10
            ) a
        )
    ) into v_result;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_get_dashboard() to authenticated;

create or replace function public.atlas_admin_list_players(p_search text default '', p_limit integer default 100)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_search text := '%' || lower(trim(coalesce(p_search,''))) || '%';
    v_limit integer := least(greatest(coalesce(p_limit,100), 1), 250);
    v_result jsonb;
begin
    v_admin := public.atlas_require_admin();

    select coalesce(jsonb_agg(to_jsonb(p)), '[]'::jsonb)
    into v_result
    from (
        select
            prof.user_id::text,
            prof.email,
            prof.username,
            prof.display_name,
            prof.avatar_emoji,
            prof.home_country,
            prof.is_public,
            coalesce(lb.score,0) as score,
            coalesce(lb.parcels,0) as parcels,
            coalesce(lb.income_per_second,0) as income_per_second,
            coalesce(lb.lifetime_earned,0) as lifetime_earned,
            coalesce(lb.empire_level,1) as empire_level,
            coalesce(w.balance,0) as balance,
            coalesce(rw.redeemable_balance,0) as redeemable_balance,
            coalesce(rw.pending_payouts,0) as pending_payouts,
            coalesce(rw.currency,'GBP') as currency,
            lb.last_seen,
            prof.updated_at
        from public.parcel_player_profiles prof
        left join public.atlas_leaderboard lb on lb.user_id = prof.user_id
        left join public.atlas_wallets w on w.user_id = prof.user_id
        left join public.atlas_reward_wallets rw on rw.user_id = prof.user_id
        where trim(coalesce(p_search,'')) = ''
            or lower(coalesce(prof.username,'')) like v_search
            or lower(coalesce(prof.display_name,'')) like v_search
            or lower(coalesce(prof.email,'')) like v_search
            or prof.user_id::text like v_search
        order by coalesce(lb.score,0) desc, prof.updated_at desc
        limit v_limit
    ) p;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_list_players(text, integer) to authenticated;

create or replace function public.atlas_admin_list_payouts(p_status text default 'pending', p_limit integer default 100)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_status text := lower(trim(coalesce(p_status,'pending')));
    v_limit integer := least(greatest(coalesce(p_limit,100), 1), 250);
    v_result jsonb;
begin
    v_admin := public.atlas_require_admin();

    select coalesce(jsonb_agg(to_jsonb(x)), '[]'::jsonb)
    into v_result
    from (
        select
            pr.id::text,
            pr.user_id::text,
            coalesce(prof.display_name, prof.username, 'Atlas Player') as display_name,
            prof.username,
            pr.amount,
            pr.currency,
            pr.status,
            pr.payout_method,
            pr.payout_target_masked,
            pr.metadata,
            pr.created_at,
            pr.updated_at
        from public.atlas_payout_requests pr
        left join public.parcel_player_profiles prof on prof.user_id = pr.user_id
        where v_status = 'all' or pr.status = v_status
        order by pr.created_at desc
        limit v_limit
    ) x;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_list_payouts(text, integer) to authenticated;

create or replace function public.atlas_admin_update_payout(p_payout_id uuid, p_status text, p_note text default null)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_status text := lower(trim(coalesce(p_status,'')));
    v_request public.atlas_payout_requests%rowtype;
    v_updated public.atlas_payout_requests%rowtype;
begin
    v_admin := public.atlas_require_admin();

    if v_status not in ('pending','approved','paid','rejected','cancelled') then
        raise exception 'Invalid payout status.';
    end if;

    select * into v_request
    from public.atlas_payout_requests
    where id = p_payout_id
    for update;

    if not found then
        raise exception 'Payout request not found.';
    end if;

    if v_request.status in ('paid','rejected','cancelled') and v_request.status <> v_status then
        raise exception 'Finalised payout requests cannot be changed.';
    end if;

    if v_request.status <> v_status then
        if v_status = 'paid' then
            update public.atlas_reward_wallets
            set pending_payouts = greatest(0, pending_payouts - v_request.amount),
                total_paid = total_paid + v_request.amount,
                updated_at = now()
            where user_id = v_request.user_id;
        elsif v_status in ('rejected','cancelled') then
            update public.atlas_reward_wallets
            set pending_payouts = greatest(0, pending_payouts - v_request.amount),
                redeemable_balance = redeemable_balance + v_request.amount,
                updated_at = now()
            where user_id = v_request.user_id;
        end if;
    end if;

    update public.atlas_payout_requests
    set status = v_status,
        metadata = coalesce(metadata,'{}'::jsonb) || jsonb_build_object('admin_note', coalesce(p_note,''), 'admin_id', v_admin, 'admin_updated_at', now()),
        updated_at = now()
    where id = p_payout_id
    returning * into v_updated;

    perform public.atlas_admin_write_audit('payout_' || v_status, 'payout_request', p_payout_id::text, jsonb_build_object('old_status', v_request.status, 'new_status', v_status, 'note', coalesce(p_note,''), 'amount', v_request.amount, 'currency', v_request.currency));

    return public.atlas_payout_request_json(v_updated);
end;
$$;

grant execute on function public.atlas_admin_update_payout(uuid, text, text) to authenticated;

create or replace function public.atlas_admin_list_events(p_limit integer default 100)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_result jsonb;
begin
    v_admin := public.atlas_require_admin();

    select coalesce(jsonb_agg(to_jsonb(e)), '[]'::jsonb)
    into v_result
    from (
        select id, title, description, metric, target, reward_type, reward_amount, reward_label, starts_at, ends_at, active, metadata, created_at, updated_at
        from public.atlas_live_events
        order by active desc, updated_at desc
        limit least(greatest(coalesce(p_limit,100),1),250)
    ) e;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_list_events(integer) to authenticated;

create or replace function public.atlas_admin_upsert_event(p_event jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_event public.atlas_live_events%rowtype;
    v_id text := lower(regexp_replace(coalesce(p_event->>'id',''), '[^a-z0-9_\-]+', '_', 'g'));
    v_reward_type text := coalesce(p_event->>'reward_type', 'game_cash');
begin
    v_admin := public.atlas_require_admin();

    if length(v_id) < 3 then
        raise exception 'Event ID is required.';
    end if;

    if v_reward_type not in ('game_cash','redeemable') then
        raise exception 'Invalid reward type.';
    end if;

    insert into public.atlas_live_events(id, title, description, metric, target, reward_type, reward_amount, reward_label, active, metadata, updated_at)
    values (
        v_id,
        left(coalesce(p_event->>'title','Untitled Event'), 120),
        left(coalesce(p_event->>'description',''), 500),
        left(coalesce(p_event->>'metric','daily_login'), 80),
        coalesce((p_event->>'target')::numeric, 1),
        v_reward_type,
        coalesce((p_event->>'reward_amount')::numeric, 0),
        coalesce(p_event->>'reward_label', null),
        coalesce((p_event->>'active')::boolean, true),
        coalesce(p_event->'metadata', '{}'::jsonb),
        now()
    )
    on conflict (id) do update set
        title = excluded.title,
        description = excluded.description,
        metric = excluded.metric,
        target = excluded.target,
        reward_type = excluded.reward_type,
        reward_amount = excluded.reward_amount,
        reward_label = excluded.reward_label,
        active = excluded.active,
        metadata = excluded.metadata,
        updated_at = now()
    returning * into v_event;

    perform public.atlas_admin_write_audit('event_upsert', 'live_event', v_event.id, to_jsonb(v_event));

    return to_jsonb(v_event);
end;
$$;

grant execute on function public.atlas_admin_upsert_event(jsonb) to authenticated;

create or replace function public.atlas_admin_list_marketplace(p_status text default 'active', p_limit integer default 100)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_status text := lower(trim(coalesce(p_status,'active')));
    v_result jsonb;
begin
    v_admin := public.atlas_require_admin();

    select coalesce(jsonb_agg(to_jsonb(m)), '[]'::jsonb)
    into v_result
    from (
        select id::text, parcel_id, seller_id::text, seller_name, buyer_id::text, price, status, parcel_snapshot, created_at, updated_at, sold_at
        from public.atlas_marketplace
        where v_status = 'all' or status = v_status
        order by created_at desc
        limit least(greatest(coalesce(p_limit,100),1),250)
    ) m;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_list_marketplace(text, integer) to authenticated;

create or replace function public.atlas_admin_update_marketplace_status(p_listing_id uuid, p_status text, p_note text default null)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_status text := lower(trim(coalesce(p_status,'')));
    v_listing public.atlas_marketplace%rowtype;
begin
    v_admin := public.atlas_require_admin();

    if v_status not in ('active','cancelled','flagged') then
        raise exception 'Admin can only set listings to active, cancelled or flagged.';
    end if;

    update public.atlas_marketplace
    set status = v_status,
        updated_at = now(),
        parcel_snapshot = coalesce(parcel_snapshot,'{}'::jsonb) || jsonb_build_object('admin_note', coalesce(p_note,''), 'admin_id', v_admin, 'admin_updated_at', now())
    where id = p_listing_id
    and status <> 'sold'
    returning * into v_listing;

    if not found then
        raise exception 'Listing not found or already sold.';
    end if;

    perform public.atlas_admin_write_audit('marketplace_' || v_status, 'marketplace_listing', p_listing_id::text, jsonb_build_object('parcel_id', v_listing.parcel_id, 'note', coalesce(p_note,'')));

    return to_jsonb(v_listing);
end;
$$;

grant execute on function public.atlas_admin_update_marketplace_status(uuid, text, text) to authenticated;

create or replace function public.atlas_admin_update_reward_rules(p_rules jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_value jsonb;
begin
    v_admin := public.atlas_require_admin();

    insert into public.atlas_app_settings(key, value, updated_at)
    values ('reward_rules', coalesce(p_rules,'{}'::jsonb), now())
    on conflict (key) do update set
        value = public.atlas_app_settings.value || excluded.value,
        updated_at = now()
    returning value into v_value;

    perform public.atlas_admin_write_audit('reward_rules_update', 'app_settings', 'reward_rules', v_value);

    return v_value;
end;
$$;

grant execute on function public.atlas_admin_update_reward_rules(jsonb) to authenticated;

create or replace function public.atlas_admin_update_real_money_settings(p_settings jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_value jsonb;
begin
    v_admin := public.atlas_require_admin();

    insert into public.atlas_app_settings(key, value, updated_at)
    values ('real_money', coalesce(p_settings,'{}'::jsonb), now())
    on conflict (key) do update set
        value = public.atlas_app_settings.value || excluded.value,
        updated_at = now()
    returning value into v_value;

    perform public.atlas_admin_write_audit('real_money_settings_update', 'app_settings', 'real_money', v_value);

    return v_value;
end;
$$;

grant execute on function public.atlas_admin_update_real_money_settings(jsonb) to authenticated;

create or replace function public.atlas_admin_list_audit(p_limit integer default 150)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_admin uuid;
    v_result jsonb;
begin
    v_admin := public.atlas_require_admin();

    select coalesce(jsonb_agg(to_jsonb(a)), '[]'::jsonb)
    into v_result
    from (
        select id, admin_id::text, action, target_type, target_id, details, created_at
        from public.atlas_admin_audit
        order by created_at desc
        limit least(greatest(coalesce(p_limit,150),1),500)
    ) a;

    return v_result;
end;
$$;

grant execute on function public.atlas_admin_list_audit(integer) to authenticated;

-- After creating your first admin user in Supabase Auth, run this manually once:
-- insert into public.atlas_admins(user_id, role, notes)
-- select id, 'super_admin', 'Initial owner admin'
-- from auth.users
-- where email = 'YOUR_ADMIN_EMAIL@example.com'
-- on conflict (user_id) do update set role = 'super_admin', is_active = true, updated_at = now();


-- =====================================================
-- PHASE 3.9 - PAYOUT PROVIDER SETUP
-- =====================================================

insert into public.atlas_app_settings(key, value)
values (
    'payout_provider',
    jsonb_build_object(
        'provider', 'not_connected',
        'provider_ready', false,
        'terms_version', 'phase3.9-payout-setup',
        'min_account_age_days', 0,
        'min_gps_trust', 70,
        'require_tax_ready', false,
        'manual_review_required', true,
        'notice', 'Payout provider setup is staged only. Do not enable real payments until legal, tax/KYC, fraud and support processes are complete.'
    )
)
on conflict (key) do update set
    value = public.atlas_app_settings.value || excluded.value,
    updated_at = now();

create table if not exists public.atlas_payout_profiles (
    user_id uuid primary key references auth.users(id) on delete cascade,
    payout_method text not null default 'paypal',
    payout_target_masked text,
    legal_name text,
    country_code text default 'GB',
    terms_version text,
    terms_accepted_at timestamptz,
    verification_status text not null default 'not_started' check (verification_status in ('not_started','pending','ready','rejected')),
    provider_account_id text,
    metadata jsonb not null default '{}'::jsonb,
    created_at timestamptz not null default now(),
    updated_at timestamptz not null default now()
);

alter table public.atlas_payout_profiles enable row level security;

drop policy if exists atlas_payout_profiles_owner_read on public.atlas_payout_profiles;
create policy atlas_payout_profiles_owner_read on public.atlas_payout_profiles for select using (auth.uid() = user_id);

create or replace function public.atlas_mask_payout_target(p_target text)
returns text
language sql
stable
as $$
    select case
        when coalesce(trim(p_target),'') = '' then null
        when position('@' in trim(p_target)) > 1 then left(trim(p_target), 2) || '***@' || split_part(trim(p_target), '@', 2)
        else left(trim(p_target), 2) || '***' || right(trim(p_target), 2)
    end;
$$;

create or replace function public.atlas_save_payout_profile(
    p_payout_method text,
    p_payout_target text,
    p_legal_name text,
    p_country_code text,
    p_terms_version text
)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
    v_user uuid := auth.uid();
    v_profile public.atlas_payout_profiles;
begin
    if v_user is null then
        raise exception 'Not authenticated.';
    end if;

    if length(trim(coalesce(p_payout_target,''))) < 4 then
        raise exception 'Payout target is required.';
    end if;

    insert into public.atlas_payout_profiles(user_id, payout_method, payout_target_masked, legal_name, country_code, terms_version, terms_accepted_at, updated_at)
    values (
        v_user,
        left(coalesce(p_payout_method, 'paypal'), 40),
        public.atlas_mask_payout_target(p_payout_target),
        left(trim(coalesce(p_legal_name,'')), 120),
        upper(left(trim(coalesce(p_country_code,'GB')), 3)),
        left(coalesce(p_terms_version, 'phase3.9-payout-setup'), 80),
        now(),
        now()
    )
    on conflict (user_id) do update set
        payout_method = excluded.payout_method,
        payout_target_masked = excluded.payout_target_masked,
        legal_name = excluded.legal_name,
        country_code = excluded.country_code,
        terms_version = excluded.terms_version,
        terms_accepted_at = excluded.terms_accepted_at,
        updated_at = now()
    returning * into v_profile;

    return jsonb_build_object(
        'payout_method', v_profile.payout_method,
        'payout_target_masked', v_profile.payout_target_masked,
        'legal_name', v_profile.legal_name,
        'country_code', v_profile.country_code,
        'verification_status', v_profile.verification_status,
        'terms_version', v_profile.terms_version,
        'updated_at', v_profile.updated_at
    );
end;
$$;

grant execute on function public.atlas_save_payout_profile(text, text, text, text, text) to authenticated;
