-- ============================================================================
-- EduAdvise CRM — Phase 1: Foundation
-- branches, roles, permissions, users, audit log
-- ============================================================================

create extension if not exists "pgcrypto";

-- ---------------------------------------------------------------------------
-- Branches (multi-branch; Head Office is just a branch with is_head_office)
-- ---------------------------------------------------------------------------
create table public.branches (
  id              uuid primary key default gen_random_uuid(),
  name            text not null,
  code            text unique,
  city            text,
  state           text,
  country         text default 'India',
  address         text,
  phone           text,
  email           text,
  is_head_office  boolean not null default false,
  is_active       boolean not null default true,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Roles (seeded but editable via Settings → Role)
-- ---------------------------------------------------------------------------
create table public.roles (
  id          uuid primary key default gen_random_uuid(),
  name        text not null unique,
  slug        text not null unique,         -- stable key used in code
  description text,
  is_system   boolean not null default false, -- system roles cannot be deleted
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- Permissions (resource.action) + role mapping
-- ---------------------------------------------------------------------------
create table public.permissions (
  id          uuid primary key default gen_random_uuid(),
  resource    text not null,                -- e.g. 'leads', 'reports.finance'
  action      text not null,                -- e.g. 'read', 'create', 'update', 'delete', 'assign'
  label       text not null,
  unique (resource, action)
);

create table public.role_permissions (
  role_id       uuid not null references public.roles(id) on delete cascade,
  permission_id uuid not null references public.permissions(id) on delete cascade,
  primary key (role_id, permission_id)
);

-- ---------------------------------------------------------------------------
-- Users — extends Supabase auth.users (1:1). One row per staff/partner login.
-- ---------------------------------------------------------------------------
create table public.users (
  id                  uuid primary key references auth.users(id) on delete cascade,
  full_name           text not null,
  email               text not null unique,
  phone               text,
  role_id             uuid references public.roles(id),
  primary_branch_id   uuid references public.branches(id),
  status              text not null default 'active',  -- active | suspended | invited
  -- B2B partner fields
  b2b_country         text,
  b2b_state           text,
  allowed_ips         text[],                          -- if set, login restricted to these IPs
  date_of_birth       date,
  joining_date        date,
  avatar_url          text,
  created_by          uuid references public.users(id),
  updated_by          uuid references public.users(id),
  created_at          timestamptz not null default now(),
  updated_at          timestamptz not null default now()
);

-- A user can operate across multiple branches (many-to-many)
create table public.user_branches (
  user_id    uuid not null references public.users(id) on delete cascade,
  branch_id  uuid not null references public.branches(id) on delete cascade,
  primary key (user_id, branch_id)
);

-- ---------------------------------------------------------------------------
-- Audit log — every mutating action writes a diff here
-- ---------------------------------------------------------------------------
create table public.audit_log (
  id          bigint generated always as identity primary key,
  table_name  text not null,
  row_id      text,
  action      text not null,                -- insert | update | delete
  actor_id    uuid references public.users(id),
  branch_id   uuid references public.branches(id),
  before      jsonb,
  after       jsonb,
  ip          text,
  user_agent  text,
  created_at  timestamptz not null default now()
);
create index audit_log_table_row_idx on public.audit_log (table_name, row_id);
create index audit_log_actor_idx on public.audit_log (actor_id);
create index audit_log_created_idx on public.audit_log (created_at desc);

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

create trigger trg_branches_updated before update on public.branches
  for each row execute function public.set_updated_at();
create trigger trg_roles_updated before update on public.roles
  for each row execute function public.set_updated_at();
create trigger trg_users_updated before update on public.users
  for each row execute function public.set_updated_at();