-- ============================================================
-- KHARJA Back Office - Admin, operations and moderation schema
-- Apply after mobile migrations 001..006.
-- ============================================================

create extension if not exists "pgcrypto";

create or replace function public.set_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- ============================================================
-- Operational columns on existing product tables
-- ============================================================

alter table public.profiles
  add column if not exists status text not null default 'active',
  add column if not exists suspended_at timestamptz,
  add column if not exists suspended_reason text,
  add column if not exists deleted_at timestamptz;

alter table public.profiles
  drop constraint if exists profiles_status_check;

alter table public.profiles
  add constraint profiles_status_check
  check (status in ('active', 'suspended', 'deleted'));

alter table public.service_providers
  add column if not exists email text,
  add column if not exists specialties text[] not null default '{}',
  add column if not exists services_offered text[] not null default '{}',
  add column if not exists suspended_at timestamptz,
  add column if not exists rejected_reason text;

alter table public.missions
  add column if not exists frequency text,
  add column if not exists conditions jsonb not null default '{}'::jsonb,
  add column if not exists starts_at timestamptz,
  add column if not exists ends_at timestamptz,
  add column if not exists updated_at timestamptz not null default now();

alter table public.missions
  drop constraint if exists missions_frequency_check;

alter table public.missions
  add constraint missions_frequency_check
  check (frequency is null or frequency in ('one_time', 'daily', 'weekly', 'monthly'));

drop trigger if exists missions_set_updated_at on public.missions;
create trigger missions_set_updated_at
before update on public.missions
for each row execute function public.set_updated_at();

alter table public.reports
  drop constraint if exists reports_target_type_check;

alter table public.reports
  add constraint reports_target_type_check
  check (target_type in ('route', 'comment', 'provider', 'user', 'review', 'photo'));

alter table public.reward_redemptions
  drop constraint if exists reward_redemptions_status_check;

alter table public.reward_redemptions
  add constraint reward_redemptions_status_check
  check (status in ('pending', 'approved', 'rejected', 'fulfilled', 'active', 'used', 'expired', 'cancelled'));

alter table public.reward_redemptions
  add column if not exists decision_reason text,
  add column if not exists reviewed_by uuid,
  add column if not exists reviewed_at timestamptz;

-- ============================================================
-- Admin identity, roles and permissions
-- ============================================================

create table if not exists public.admin_roles (
  key text primary key,
  name text not null,
  description text,
  is_system boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

drop trigger if exists admin_roles_set_updated_at on public.admin_roles;
create trigger admin_roles_set_updated_at
before update on public.admin_roles
for each row execute function public.set_updated_at();

create table if not exists public.admin_permissions (
  key text primary key,
  name text not null,
  description text,
  resource text not null,
  action text not null,
  created_at timestamptz not null default now()
);

create table if not exists public.admin_role_permissions (
  role_key text not null references public.admin_roles(key) on delete cascade,
  permission_key text not null references public.admin_permissions(key) on delete cascade,
  created_at timestamptz not null default now(),
  primary key (role_key, permission_key)
);

create table if not exists public.admin_users (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references auth.users(id) on delete cascade,
  role_key text not null references public.admin_roles(key),
  display_name text,
  email text not null,
  status text not null default 'active',
  last_seen_at timestamptz,
  created_by uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint admin_users_status_check check (status in ('active', 'disabled', 'invited'))
);

create index if not exists admin_users_user_idx on public.admin_users(user_id);
create index if not exists admin_users_role_idx on public.admin_users(role_key);

drop trigger if exists admin_users_set_updated_at on public.admin_users;
create trigger admin_users_set_updated_at
before update on public.admin_users
for each row execute function public.set_updated_at();

create table if not exists public.admin_sessions (
  id uuid primary key default gen_random_uuid(),
  admin_user_id uuid not null references public.admin_users(id) on delete cascade,
  auth_user_id uuid not null references auth.users(id) on delete cascade,
  ip_address text,
  user_agent text,
  started_at timestamptz not null default now(),
  ended_at timestamptz,
  last_seen_at timestamptz not null default now()
);

create index if not exists admin_sessions_admin_idx on public.admin_sessions(admin_user_id, started_at desc);

create table if not exists public.admin_audit_logs (
  id uuid primary key default gen_random_uuid(),
  admin_user_id uuid references public.admin_users(id) on delete set null,
  action text not null,
  entity_type text not null,
  entity_id text,
  old_values jsonb,
  new_values jsonb,
  ip_address text,
  user_agent text,
  created_at timestamptz not null default now()
);

create index if not exists admin_audit_logs_admin_idx on public.admin_audit_logs(admin_user_id, created_at desc);
create index if not exists admin_audit_logs_entity_idx on public.admin_audit_logs(entity_type, entity_id);

create table if not exists public.admin_notes (
  id uuid primary key default gen_random_uuid(),
  admin_user_id uuid references public.admin_users(id) on delete set null,
  entity_type text not null,
  entity_id text not null,
  note text not null,
  visibility text not null default 'internal',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint admin_notes_visibility_check check (visibility in ('internal', 'support', 'moderation', 'partner'))
);

create index if not exists admin_notes_entity_idx on public.admin_notes(entity_type, entity_id, created_at desc);

drop trigger if exists admin_notes_set_updated_at on public.admin_notes;
create trigger admin_notes_set_updated_at
before update on public.admin_notes
for each row execute function public.set_updated_at();

-- ============================================================
-- Content, campaigns, support and partner operations
-- ============================================================

create table if not exists public.content_flags (
  id uuid primary key default gen_random_uuid(),
  entity_type text not null,
  entity_id uuid not null,
  flag_type text not null,
  label text,
  metadata jsonb not null default '{}'::jsonb,
  starts_at timestamptz,
  ends_at timestamptz,
  created_by uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint content_flags_type_check check (flag_type in ('featured', 'hidden', 'editorial', 'sponsored')),
  unique (entity_type, entity_id, flag_type)
);

create index if not exists content_flags_entity_idx on public.content_flags(entity_type, entity_id);

drop trigger if exists content_flags_set_updated_at on public.content_flags;
create trigger content_flags_set_updated_at
before update on public.content_flags
for each row execute function public.set_updated_at();

create table if not exists public.campaigns (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  title text not null,
  body text not null,
  type text not null default 'system',
  deep_link text,
  audience jsonb not null default '{}'::jsonb,
  status text not null default 'draft',
  scheduled_at timestamptz,
  sent_at timestamptz,
  created_by uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint campaigns_status_check check (status in ('draft', 'scheduled', 'sent', 'failed', 'cancelled')),
  constraint campaigns_type_check check (type in ('system', 'trip', 'maintenance', 'reward', 'community', 'security', 'partner'))
);

create index if not exists campaigns_status_idx on public.campaigns(status, scheduled_at);

drop trigger if exists campaigns_set_updated_at on public.campaigns;
create trigger campaigns_set_updated_at
before update on public.campaigns
for each row execute function public.set_updated_at();

create table if not exists public.campaign_audiences (
  id uuid primary key default gen_random_uuid(),
  campaign_id uuid not null references public.campaigns(id) on delete cascade,
  segment_type text not null,
  filters jsonb not null default '{}'::jsonb,
  estimated_count int,
  created_at timestamptz not null default now()
);

create index if not exists campaign_audiences_campaign_idx on public.campaign_audiences(campaign_id);

create table if not exists public.partner_contracts (
  id uuid primary key default gen_random_uuid(),
  provider_id uuid references public.service_providers(id) on delete set null,
  company_name text not null,
  contact_name text,
  contact_email text,
  contact_phone text,
  status text not null default 'prospect',
  contract_type text not null default 'free',
  monthly_price numeric(10,2),
  starts_at date,
  ends_at date,
  notes text,
  assigned_admin_id uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint partner_contracts_status_check check (status in ('prospect', 'contacted', 'negotiating', 'active', 'paused', 'cancelled')),
  constraint partner_contracts_type_check check (contract_type in ('free', 'premium', 'sponsored', 'coupon_partner'))
);

create index if not exists partner_contracts_provider_idx on public.partner_contracts(provider_id);
create index if not exists partner_contracts_status_idx on public.partner_contracts(status);

drop trigger if exists partner_contracts_set_updated_at on public.partner_contracts;
create trigger partner_contracts_set_updated_at
before update on public.partner_contracts
for each row execute function public.set_updated_at();

create table if not exists public.service_provider_coupons (
  id uuid primary key default gen_random_uuid(),
  provider_id uuid not null references public.service_providers(id) on delete cascade,
  code text not null,
  title text not null,
  description text,
  discount_type text not null default 'fixed',
  discount_value numeric(10,2),
  starts_at timestamptz,
  ends_at timestamptz,
  usage_limit int,
  used_count int not null default 0,
  is_active boolean not null default true,
  created_by uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (provider_id, code),
  constraint service_provider_coupons_discount_check check (discount_type in ('fixed', 'percent', 'custom'))
);

drop trigger if exists service_provider_coupons_set_updated_at on public.service_provider_coupons;
create trigger service_provider_coupons_set_updated_at
before update on public.service_provider_coupons
for each row execute function public.set_updated_at();

create table if not exists public.support_tickets (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  created_by_admin_id uuid references public.admin_users(id) on delete set null,
  assigned_admin_id uuid references public.admin_users(id) on delete set null,
  subject text not null,
  description text,
  category text not null default 'other',
  priority text not null default 'medium',
  status text not null default 'open',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  closed_at timestamptz,
  constraint support_tickets_category_check check (category in ('account', 'trip', 'garage', 'service', 'rewards', 'bug', 'payment_future', 'other')),
  constraint support_tickets_priority_check check (priority in ('low', 'medium', 'high', 'urgent')),
  constraint support_tickets_status_check check (status in ('open', 'pending', 'resolved', 'closed'))
);

create index if not exists support_tickets_status_idx on public.support_tickets(status, priority, created_at desc);
create index if not exists support_tickets_user_idx on public.support_tickets(user_id);

drop trigger if exists support_tickets_set_updated_at on public.support_tickets;
create trigger support_tickets_set_updated_at
before update on public.support_tickets
for each row execute function public.set_updated_at();

create table if not exists public.product_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references public.profiles(id) on delete set null,
  event_name text not null,
  event_properties jsonb not null default '{}'::jsonb,
  platform text,
  created_at timestamptz not null default now()
);

create index if not exists product_events_name_time_idx on public.product_events(event_name, created_at desc);
create index if not exists product_events_user_time_idx on public.product_events(user_id, created_at desc);

create table if not exists public.checklist_templates (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title text not null,
  description text,
  category text not null,
  vehicle_target text not null default 'both',
  is_active boolean not null default true,
  sort_order int not null default 0,
  created_by uuid references public.admin_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint checklist_templates_vehicle_check check (vehicle_target in ('moto', 'auto', 'both'))
);

drop trigger if exists checklist_templates_set_updated_at on public.checklist_templates;
create trigger checklist_templates_set_updated_at
before update on public.checklist_templates
for each row execute function public.set_updated_at();

create table if not exists public.checklist_template_items (
  id uuid primary key default gen_random_uuid(),
  template_id uuid not null references public.checklist_templates(id) on delete cascade,
  label text not null,
  category text,
  sort_order int not null default 0,
  is_required boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists checklist_template_items_template_idx on public.checklist_template_items(template_id, sort_order);

drop trigger if exists checklist_template_items_set_updated_at on public.checklist_template_items;
create trigger checklist_template_items_set_updated_at
before update on public.checklist_template_items
for each row execute function public.set_updated_at();

-- ============================================================
-- Seed roles and permissions
-- ============================================================

insert into public.admin_roles (key, name, description) values
  ('admin', 'Administrateur', 'Acces complet au back office KHARJA.'),
  ('moderator', 'Moderateur', 'Moderation des contenus, reports, routes, commentaires, reviews et services.'),
  ('support', 'Support', 'Lecture support des donnees utilisateur, notes internes et tickets.'),
  ('partner_manager', 'Partner manager', 'Gestion des prestataires, contrats, coupons et listings sponsorises.'),
  ('content_manager', 'Content manager', 'Gestion des contenus, templates, missions, badges et mises en avant.')
on conflict (key) do update set name = excluded.name, description = excluded.description;

insert into public.admin_permissions (key, name, resource, action, description) values
  ('dashboard.read', 'Voir dashboard', 'dashboard', 'read', 'Consulter les indicateurs globaux.'),
  ('users.read', 'Lire utilisateurs', 'users', 'read', 'Consulter les profils et activites utilisateur.'),
  ('users.write', 'Modifier utilisateurs', 'users', 'write', 'Suspendre, reactiver, changer role ou statut utilisateur.'),
  ('routes.read', 'Lire routes', 'routes', 'read', 'Consulter les routes communautaires.'),
  ('routes.moderate', 'Moderer routes', 'routes', 'moderate', 'Archiver, masquer, approuver ou mettre en avant des routes.'),
  ('trips.read', 'Lire trajets', 'trips', 'read', 'Consulter les trajets pour support.'),
  ('services.read', 'Lire services', 'services', 'read', 'Consulter les prestataires.'),
  ('services.write', 'Modifier services', 'services', 'write', 'Creer, modifier et moderer des prestataires.'),
  ('maintenance.read', 'Lire garage', 'maintenance', 'read', 'Consulter les donnees garage et maintenance.'),
  ('maintenance.write', 'Corriger garage', 'maintenance', 'write', 'Corriger des donnees garage avec audit.'),
  ('checklists.read', 'Lire checklists', 'checklists', 'read', 'Consulter les templates et checklists.'),
  ('checklists.write', 'Modifier templates', 'checklists', 'write', 'Gerer les templates publics de checklist.'),
  ('rewards.read', 'Lire rewards', 'rewards', 'read', 'Consulter rewards, badges et redemptions.'),
  ('rewards.write', 'Modifier rewards', 'rewards', 'write', 'Gerer la boutique, les redemptions et corrections XP/coins.'),
  ('missions.read', 'Lire missions', 'missions', 'read', 'Consulter les missions.'),
  ('missions.write', 'Modifier missions', 'missions', 'write', 'Gerer les missions.'),
  ('notifications.read', 'Lire notifications', 'notifications', 'read', 'Consulter logs et campagnes.'),
  ('notifications.write', 'Envoyer notifications', 'notifications', 'write', 'Creer campagnes et notifications systeme.'),
  ('reports.read', 'Lire reports', 'reports', 'read', 'Consulter les signalements.'),
  ('reports.moderate', 'Traiter reports', 'reports', 'moderate', 'Traiter les signalements et actions de moderation.'),
  ('settings.read', 'Lire settings', 'settings', 'read', 'Consulter les parametres app.'),
  ('settings.write', 'Modifier settings', 'settings', 'write', 'Modifier feature flags, versions, liens et support.'),
  ('analytics.read', 'Lire analytics', 'analytics', 'read', 'Consulter les analyses produit.'),
  ('support.read', 'Lire support', 'support', 'read', 'Consulter les tickets support.'),
  ('support.write', 'Modifier support', 'support', 'write', 'Creer et modifier les tickets et notes support.'),
  ('partners.read', 'Lire partenaires', 'partners', 'read', 'Consulter le CRM partenaires.'),
  ('partners.write', 'Modifier partenaires', 'partners', 'write', 'Gerer contrats et notes commerciales.'),
  ('admins.manage', 'Gerer admins', 'admins', 'manage', 'Gerer roles et comptes administrateurs.'),
  ('audit.read', 'Lire audit', 'audit', 'read', 'Consulter les logs admin.')
on conflict (key) do update set
  name = excluded.name,
  resource = excluded.resource,
  action = excluded.action,
  description = excluded.description;

insert into public.admin_role_permissions (role_key, permission_key)
select 'admin', key from public.admin_permissions
on conflict do nothing;

insert into public.admin_role_permissions (role_key, permission_key) values
  ('moderator', 'dashboard.read'),
  ('moderator', 'routes.read'),
  ('moderator', 'routes.moderate'),
  ('moderator', 'services.read'),
  ('moderator', 'services.write'),
  ('moderator', 'reports.read'),
  ('moderator', 'reports.moderate'),
  ('moderator', 'users.read'),
  ('moderator', 'audit.read'),
  ('support', 'dashboard.read'),
  ('support', 'users.read'),
  ('support', 'trips.read'),
  ('support', 'maintenance.read'),
  ('support', 'checklists.read'),
  ('support', 'support.read'),
  ('support', 'support.write'),
  ('support', 'reports.read'),
  ('partner_manager', 'dashboard.read'),
  ('partner_manager', 'services.read'),
  ('partner_manager', 'services.write'),
  ('partner_manager', 'partners.read'),
  ('partner_manager', 'partners.write'),
  ('partner_manager', 'reports.read'),
  ('content_manager', 'dashboard.read'),
  ('content_manager', 'routes.read'),
  ('content_manager', 'routes.moderate'),
  ('content_manager', 'checklists.read'),
  ('content_manager', 'checklists.write'),
  ('content_manager', 'missions.read'),
  ('content_manager', 'missions.write'),
  ('content_manager', 'rewards.read'),
  ('content_manager', 'rewards.write'),
  ('content_manager', 'settings.read')
on conflict do nothing;

insert into public.app_settings (key, value, notes) values
  ('maintenance_mode', '{"enabled":false,"message":""}'::jsonb, 'Back-office managed maintenance mode'),
  ('legal_links', '{"privacy_url":"https://kharja.ma/privacy","terms_url":"https://kharja.ma/terms"}'::jsonb, 'Legal links'),
  ('support_channels', '{"email":"support@kharja.ma","whatsapp":""}'::jsonb, 'Support contact channels'),
  ('operations_flags', '{"services_auto":true,"services_moto":true,"rewards":true,"community_routes":true,"notifications":true}'::jsonb, 'Operational feature flags'),
  ('xp_config', '{"trip_created":25,"route_published":80,"maintenance_completed":60,"checklist_completed":35}'::jsonb, 'XP rules'),
  ('coins_config', '{"trip_created":15,"route_published":50,"maintenance_completed":40,"checklist_completed":20}'::jsonb, 'Coins rules')
on conflict (key) do nothing;

-- ============================================================
-- RLS posture: browser can only prove "I am an active admin".
-- Sensitive reads/writes go through server service_role.
-- ============================================================

alter table public.admin_roles enable row level security;
alter table public.admin_permissions enable row level security;
alter table public.admin_role_permissions enable row level security;
alter table public.admin_users enable row level security;
alter table public.admin_sessions enable row level security;
alter table public.admin_audit_logs enable row level security;
alter table public.admin_notes enable row level security;
alter table public.content_flags enable row level security;
alter table public.campaigns enable row level security;
alter table public.campaign_audiences enable row level security;
alter table public.partner_contracts enable row level security;
alter table public.service_provider_coupons enable row level security;
alter table public.support_tickets enable row level security;
alter table public.product_events enable row level security;
alter table public.checklist_templates enable row level security;
alter table public.checklist_template_items enable row level security;

drop policy if exists "admin_users_self_read" on public.admin_users;
create policy "admin_users_self_read"
on public.admin_users for select
using (auth.uid() = user_id and status = 'active');

drop policy if exists "admin_roles_self_read" on public.admin_roles;
create policy "admin_roles_self_read"
on public.admin_roles for select
using (exists (
  select 1 from public.admin_users au
  where au.user_id = auth.uid() and au.status = 'active' and au.role_key = admin_roles.key
));

drop policy if exists "admin_role_permissions_self_read" on public.admin_role_permissions;
create policy "admin_role_permissions_self_read"
on public.admin_role_permissions for select
using (exists (
  select 1 from public.admin_users au
  where au.user_id = auth.uid() and au.status = 'active' and au.role_key = admin_role_permissions.role_key
));

drop policy if exists "admin_permissions_self_read" on public.admin_permissions;
create policy "admin_permissions_self_read"
on public.admin_permissions for select
using (exists (
  select 1
  from public.admin_users au
  join public.admin_role_permissions arp on arp.role_key = au.role_key
  where au.user_id = auth.uid()
    and au.status = 'active'
    and arp.permission_key = admin_permissions.key
));

-- Everything below intentionally has no permissive browser policy.
-- The Next.js server uses SUPABASE_SERVICE_ROLE_KEY and writes audit logs.