-- =========================================================
-- Gold Wallet Institutional Admin Portal — Full Schema
-- =========================================================

-- ========================
-- ENUMS
-- ========================
do $$ begin
  create type admin_role as enum ('super_admin','operations_admin','compliance_officer','finance_admin','support_agent');
exception when duplicate_object then null; end $$;

do $$ begin
  create type customer_tier as enum ('retail','premium','institutional');
exception when duplicate_object then null; end $$;

do $$ begin
  create type customer_status as enum ('active','suspended','frozen','under_review');
exception when duplicate_object then null; end $$;

do $$ begin
  create type kyc_status as enum ('verified','pending','under_review','rejected','expired','new');
exception when duplicate_object then null; end $$;

do $$ begin
  create type risk_level as enum ('low','medium','high','critical');
exception when duplicate_object then null; end $$;

do $$ begin
  create type order_side as enum ('buy','sell');
exception when duplicate_object then null; end $$;

do $$ begin
  create type order_status as enum ('pending','completed','failed','incident','cancelled');
exception when duplicate_object then null; end $$;

do $$ begin
  create type transfer_status as enum ('pending','successful','flagged','failed');
exception when duplicate_object then null; end $$;

do $$ begin
  create type redemption_status as enum ('pending','reserved','shipped','delivered','blocked','cancelled');
exception when duplicate_object then null; end $$;

do $$ begin
  create type inventory_status as enum ('adequate','low','critical');
exception when duplicate_object then null; end $$;

do $$ begin
  create type source_status as enum ('operational','degraded','down');
exception when duplicate_object then null; end $$;

do $$ begin
  create type case_status as enum ('new','in_progress','pending_customer','resolved');
exception when duplicate_object then null; end $$;

do $$ begin
  create type case_priority as enum ('low','medium','high','critical');
exception when duplicate_object then null; end $$;

do $$ begin
  create type message_channel as enum ('email','sms','push');
exception when duplicate_object then null; end $$;

do $$ begin
  create type message_status as enum ('delivered','pending','failed');
exception when duplicate_object then null; end $$;

do $$ begin
  create type exception_status as enum ('under_review','resolved','escalated');
exception when duplicate_object then null; end $$;

do $$ begin
  create type audit_result as enum ('success','failure','warning');
exception when duplicate_object then null; end $$;

-- ========================
-- ADMIN PROFILES (linked to auth.users)
-- ========================
create table if not exists public.admin_profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  full_name text not null,
  role admin_role not null default 'support_agent',
  department text,
  avatar_initials text,
  two_factor_enabled boolean not null default true,
  last_login_at timestamptz,
  last_login_ip text,
  created_at timestamptz not null default now()
);

-- ========================
-- CUSTOMERS
-- ========================
create table if not exists public.customers (
  id uuid primary key default gen_random_uuid(),
  customer_code text unique not null,
  name text not null,
  tier customer_tier not null default 'retail',
  status customer_status not null default 'active',
  kyc_status kyc_status not null default 'pending',
  primary_contact_name text,
  email text,
  phone text,
  risk_score int not null default 0,
  two_factor_enabled boolean not null default false,
  linked_devices int not null default 0,
  last_login_at timestamptz,
  last_login_ip text,
  registered_at timestamptz not null default now(),
  notes text
);

create index if not exists customers_status_idx on public.customers(status);
create index if not exists customers_kyc_idx on public.customers(kyc_status);
create index if not exists customers_tier_idx on public.customers(tier);

-- ========================
-- KYC APPLICATIONS
-- ========================
create table if not exists public.kyc_applications (
  id uuid primary key default gen_random_uuid(),
  app_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  status kyc_status not null default 'pending',
  risk_level risk_level not null default 'low',
  assigned_to uuid references public.admin_profiles(id) on delete set null,
  sla_minutes_remaining int,
  flags text[],
  document_proof_url text,
  selfie_url text,
  extracted_dob text,
  declared_dob text,
  mismatch_reason text,
  created_at timestamptz not null default now()
);

-- ========================
-- CASH WALLETS
-- ========================
create table if not exists public.cash_wallets (
  id uuid primary key default gen_random_uuid(),
  wallet_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  currency text not null default 'USD',
  available_balance numeric(18,2) not null default 0,
  held_balance numeric(18,2) not null default 0,
  created_at timestamptz not null default now()
);

-- ========================
-- GOLD WALLETS
-- ========================
create table if not exists public.gold_wallets (
  id uuid primary key default gen_random_uuid(),
  wallet_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  available_grams numeric(18,4) not null default 0,
  held_grams numeric(18,4) not null default 0,
  purity text not null default '99.99',
  created_at timestamptz not null default now()
);

-- ========================
-- WALLET MOVEMENTS
-- ========================
create table if not exists public.wallet_movements (
  id uuid primary key default gen_random_uuid(),
  wallet_code text not null,
  asset text not null check (asset in ('cash','gold')),
  direction text not null check (direction in ('in','out')),
  amount numeric(18,4) not null,
  balance_after numeric(18,4),
  status text not null default 'settled',
  reference text,
  note text,
  created_at timestamptz not null default now()
);

create index if not exists wallet_movements_wallet_idx on public.wallet_movements(wallet_code);

-- ========================
-- ORDERS
-- ========================
create table if not exists public.orders (
  id uuid primary key default gen_random_uuid(),
  order_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  side order_side not null,
  quantity numeric(18,4) not null,
  quoted_price numeric(18,2) not null,
  base_price numeric(18,2),
  spread numeric(18,2),
  total_amount numeric(18,2) not null,
  platform_fee numeric(18,2) default 0,
  network_fee numeric(18,2) default 0,
  status order_status not null default 'pending',
  settlement_status text,
  payment_method text,
  incident_reason text,
  created_at timestamptz not null default now()
);

create index if not exists orders_customer_idx on public.orders(customer_id);
create index if not exists orders_status_idx on public.orders(status);

-- ========================
-- QUOTES
-- ========================
create table if not exists public.quotes (
  id uuid primary key default gen_random_uuid(),
  quote_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  side order_side not null,
  price numeric(18,2) not null,
  expires_at timestamptz,
  status text not null default 'active',
  linked_order_code text,
  created_at timestamptz not null default now()
);

-- ========================
-- TRANSFERS
-- ========================
create table if not exists public.transfers (
  id uuid primary key default gen_random_uuid(),
  transfer_code text unique not null,
  sender_id uuid references public.customers(id) on delete cascade,
  recipient_id uuid references public.customers(id) on delete cascade,
  asset text not null check (asset in ('gold','cash')),
  quantity numeric(18,4) not null,
  status transfer_status not null default 'pending',
  risk_level risk_level,
  compliance_flag text,
  balance_check_passed boolean default true,
  aml_check_passed boolean default true,
  initiated_at timestamptz not null default now(),
  completed_at timestamptz
);

create index if not exists transfers_status_idx on public.transfers(status);

-- ========================
-- REDEMPTION REQUESTS
-- ========================
create table if not exists public.redemption_requests (
  id uuid primary key default gen_random_uuid(),
  code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  product_name text not null,
  weight_grams numeric(18,4) not null,
  units int not null default 1,
  purity text,
  status redemption_status not null default 'pending',
  courier text,
  tracking_number text,
  destination_address text,
  vault_location text,
  requested_at timestamptz not null default now(),
  shipped_at timestamptz,
  delivered_at timestamptz
);

-- ========================
-- VAULT INVENTORY
-- ========================
create table if not exists public.vault_inventory (
  id uuid primary key default gen_random_uuid(),
  sku text unique not null,
  product_name text not null,
  vault_location text not null,
  purity text not null,
  available_units int not null default 0,
  reserved_units int not null default 0,
  pending_units int not null default 0,
  status inventory_status not null default 'adequate',
  last_audit_at timestamptz
);

create table if not exists public.vault_activity (
  id uuid primary key default gen_random_uuid(),
  vault_location text,
  activity_type text not null,
  description text not null,
  carrier text,
  authorized_by text,
  created_at timestamptz not null default now()
);

-- ========================
-- PRICING
-- ========================
create table if not exists public.pricing_sources (
  id uuid primary key default gen_random_uuid(),
  name text unique not null,
  latency_ms int not null default 0,
  status source_status not null default 'operational',
  is_primary boolean not null default false,
  priority int not null default 1
);

create table if not exists public.price_ticks (
  id uuid primary key default gen_random_uuid(),
  spot_price numeric(18,2) not null,
  buy_price numeric(18,2) not null,
  sell_price numeric(18,2) not null,
  spread_bps numeric(10,2),
  source text,
  captured_at timestamptz not null default now()
);

create table if not exists public.pricing_config (
  id int primary key default 1,
  base_spread_pct numeric(10,4) not null default 0.20,
  volatility_markup_pct numeric(10,4) not null default 0.05,
  quote_ttl_seconds int not null default 30,
  manual_override_price numeric(18,2),
  auto_pricing_enabled boolean not null default true,
  updated_at timestamptz not null default now()
);

-- ========================
-- FEES & LIMITS
-- ========================
create table if not exists public.fee_rules (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  applies_to text not null,
  fixed_amount numeric(18,2) default 0,
  pct_amount numeric(10,4) default 0,
  status text not null default 'active',
  priority int not null default 10
);

create table if not exists public.tier_limits (
  tier customer_tier primary key,
  max_daily_buy numeric(18,2) not null,
  max_transfer numeric(18,2),
  withdrawal_ceiling numeric(18,2) not null
);

create table if not exists public.commission_rules (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  rate_pct numeric(10,4) not null,
  min_tier customer_tier not null default 'retail',
  status text not null default 'active'
);

-- ========================
-- LEDGER & RECONCILIATION
-- ========================
create table if not exists public.ledger_entries (
  id uuid primary key default gen_random_uuid(),
  entry_code text unique not null,
  account_ref text not null,
  debit numeric(18,2) default 0,
  credit numeric(18,2) default 0,
  currency text default 'USD',
  transaction_ref text,
  description text,
  created_at timestamptz not null default now()
);

create table if not exists public.reconciliation_exceptions (
  id uuid primary key default gen_random_uuid(),
  exception_code text unique not null,
  source text not null,
  transaction_ref text,
  internal_value numeric(18,2),
  external_value numeric(18,2),
  difference numeric(18,2),
  severity risk_level not null default 'medium',
  status exception_status not null default 'under_review',
  age_hours int not null default 0,
  suggested_cause text,
  assigned_to text,
  created_at timestamptz not null default now()
);

-- ========================
-- AUDIT LOGS
-- ========================
create table if not exists public.audit_logs (
  id uuid primary key default gen_random_uuid(),
  event_code text unique not null,
  actor_name text not null,
  actor_role text,
  action text not null,
  target_entity text,
  module text,
  result audit_result not null default 'success',
  ip_address text,
  device text,
  before_value jsonb,
  after_value jsonb,
  reason text,
  created_at timestamptz not null default now()
);

create index if not exists audit_logs_module_idx on public.audit_logs(module);
create index if not exists audit_logs_created_idx on public.audit_logs(created_at desc);

-- ========================
-- NOTIFICATIONS
-- ========================
create table if not exists public.message_templates (
  id uuid primary key default gen_random_uuid(),
  name text unique not null,
  channel message_channel not null,
  subject text,
  body text,
  updated_at timestamptz not null default now()
);

create table if not exists public.messages (
  id uuid primary key default gen_random_uuid(),
  message_code text unique not null,
  customer_id uuid references public.customers(id) on delete set null,
  channel message_channel not null,
  template_name text,
  trigger_type text,
  status message_status not null default 'pending',
  sent_at timestamptz
);

-- ========================
-- SUPPORT CASES
-- ========================
create table if not exists public.support_cases (
  id uuid primary key default gen_random_uuid(),
  case_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  category text not null,
  priority case_priority not null default 'medium',
  status case_status not null default 'new',
  linked_ref text,
  assigned_to uuid references public.admin_profiles(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- ========================
-- RISK / AML ALERTS
-- ========================
create table if not exists public.risk_alerts (
  id uuid primary key default gen_random_uuid(),
  alert_code text unique not null,
  customer_id uuid references public.customers(id) on delete cascade,
  rule_name text not null,
  severity risk_level not null default 'medium',
  status text not null default 'open',
  reference text,
  details jsonb,
  created_at timestamptz not null default now()
);

-- ========================
-- ROLES & PERMISSIONS
-- ========================
create table if not exists public.roles (
  id uuid primary key default gen_random_uuid(),
  name text unique not null,
  description text,
  is_immutable boolean not null default false,
  user_count int not null default 0
);

create table if not exists public.role_permissions (
  id uuid primary key default gen_random_uuid(),
  role_id uuid references public.roles(id) on delete cascade,
  module text not null,
  can_view boolean not null default false,
  can_create boolean not null default false,
  can_edit boolean not null default false,
  can_delete boolean not null default false,
  can_execute boolean not null default false,
  unique (role_id, module)
);

-- ========================
-- SYSTEM SETTINGS
-- ========================
create table if not exists public.system_settings (
  id int primary key default 1,
  environment text not null default 'Production',
  maintenance_mode boolean not null default false,
  mfa_policy text not null default 'Strict (All Users)',
  session_timeout_minutes int not null default 15,
  admin_ip_whitelist text[] default array[]::text[],
  default_quote_ttl int not null default 30,
  base_spread_bps numeric(10,2) not null default 25,
  max_slippage_pct numeric(10,4) not null default 1.5,
  engine_version text default 'v4.2.1-stable',
  last_deployment_at timestamptz default now(),
  updated_at timestamptz not null default now(),
  updated_by text
);

create table if not exists public.integrations_health (
  id uuid primary key default gen_random_uuid(),
  name text unique not null,
  category text,
  status source_status not null default 'operational',
  latency_ms int,
  last_sync_at timestamptz,
  note text
);

-- ========================
-- CUSTOMER NOTES
-- ========================
create table if not exists public.customer_notes (
  id uuid primary key default gen_random_uuid(),
  customer_id uuid references public.customers(id) on delete cascade,
  author_name text,
  content text not null,
  created_at timestamptz not null default now()
);

-- ========================
-- RLS POLICIES
-- ========================
alter table public.admin_profiles enable row level security;
alter table public.customers enable row level security;
alter table public.kyc_applications enable row level security;
alter table public.cash_wallets enable row level security;
alter table public.gold_wallets enable row level security;
alter table public.wallet_movements enable row level security;
alter table public.orders enable row level security;
alter table public.quotes enable row level security;
alter table public.transfers enable row level security;
alter table public.redemption_requests enable row level security;
alter table public.vault_inventory enable row level security;
alter table public.vault_activity enable row level security;
alter table public.pricing_sources enable row level security;
alter table public.price_ticks enable row level security;
alter table public.pricing_config enable row level security;
alter table public.fee_rules enable row level security;
alter table public.tier_limits enable row level security;
alter table public.commission_rules enable row level security;
alter table public.ledger_entries enable row level security;
alter table public.reconciliation_exceptions enable row level security;
alter table public.audit_logs enable row level security;
alter table public.message_templates enable row level security;
alter table public.messages enable row level security;
alter table public.support_cases enable row level security;
alter table public.risk_alerts enable row level security;
alter table public.roles enable row level security;
alter table public.role_permissions enable row level security;
alter table public.system_settings enable row level security;
alter table public.integrations_health enable row level security;
alter table public.customer_notes enable row level security;

-- Helper: is authenticated admin?
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (select 1 from public.admin_profiles where id = auth.uid());
$$;

-- Generic "any authenticated admin can read + write" policies for every table
do $$
declare
  t text;
  tables text[] := array[
    'customers','kyc_applications','cash_wallets','gold_wallets','wallet_movements',
    'orders','quotes','transfers','redemption_requests','vault_inventory','vault_activity',
    'pricing_sources','price_ticks','pricing_config','fee_rules','tier_limits','commission_rules',
    'ledger_entries','reconciliation_exceptions','audit_logs','message_templates','messages',
    'support_cases','risk_alerts','roles','role_permissions','system_settings',
    'integrations_health','customer_notes'
  ];
begin
  foreach t in array tables loop
    execute format('drop policy if exists "%s_admin_all" on public.%I', t, t);
    execute format('create policy "%s_admin_all" on public.%I for all using (auth.role() = ''authenticated'') with check (auth.role() = ''authenticated'')', t, t);
  end loop;
end $$;

-- Admin profiles: users can read their own profile; any auth'd user can read all admin profiles
drop policy if exists "admin_profiles_select_all" on public.admin_profiles;
create policy "admin_profiles_select_all" on public.admin_profiles
  for select using (auth.role() = 'authenticated');

drop policy if exists "admin_profiles_update_own" on public.admin_profiles;
create policy "admin_profiles_update_own" on public.admin_profiles
  for update using (auth.uid() = id);

drop policy if exists "admin_profiles_insert_own" on public.admin_profiles;
create policy "admin_profiles_insert_own" on public.admin_profiles
  for insert with check (auth.uid() = id);

-- ========================
-- Trigger: auto-create admin_profile on signup
-- ========================
create or replace function public.handle_new_admin_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.admin_profiles (id, full_name, role, avatar_initials)
  values (
    new.id,
    coalesce(new.raw_user_meta_data ->> 'full_name', split_part(new.email, '@', 1)),
    coalesce((new.raw_user_meta_data ->> 'role')::admin_role, 'super_admin'::admin_role),
    upper(left(coalesce(new.raw_user_meta_data ->> 'full_name', new.email), 2))
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

drop trigger if exists on_admin_user_created on auth.users;
create trigger on_admin_user_created
  after insert on auth.users
  for each row execute function public.handle_new_admin_user();
