-- =============================================================================
-- Migration: public.users + first super_admin (no separate "admin" table)
-- =============================================================================
-- This app stores admins in `public.users` with role = 'super_admin'.
-- Run once in Supabase SQL Editor (or psql) against your live DB.
--
-- Default seeded login (CHANGE EMAIL + PASSWORD IN PRODUCTION):
--   Email:    superadmin@example.com
--   Password: ChangeMe123!
--
-- To use your own password, generate a bcrypt hash (12 rounds) from backend:
--   node -e "const b=require('bcryptjs'); b.hash('YourPassword',12).then(console.log)"
-- Then replace the hash in the INSERT below.
-- =============================================================================

create extension if not exists "pgcrypto";

-- Core users table
create table if not exists public.users (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  email text not null unique,
  password_hash text not null,
  role text not null,
  is_active boolean not null default true,
  invited_at timestamptz null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  created_by uuid null references public.users (id) on delete set null
);

-- Optional / later columns (safe if already present)
alter table public.users
  add column if not exists team_name text null;

alter table public.users
  add column if not exists team_lead_id uuid null;

-- Add FK for team_lead_id only when column exists and FK not present
do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'users_team_lead_id_fkey'
  ) then
    alter table public.users
      add constraint users_team_lead_id_fkey
      foreign key (team_lead_id) references public.users (id) on delete set null;
  end if;
exception
  when duplicate_object then null;
end $$;

alter table public.users
  add column if not exists avatar_url text null;

-- Role whitelist (matches backend ROLES)
alter table public.users
  drop constraint if exists users_role_check;

alter table public.users
  add constraint users_role_check
  check (role in ('super_admin', 'manager', 'team_lead', 'employee'));

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

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

create index if not exists idx_users_email on public.users (email);
create index if not exists idx_users_role on public.users (role);
create index if not exists idx_users_team_lead_id on public.users (team_lead_id);

-- -----------------------------------------------------------------------------
-- Seed: one super_admin (skipped if email already exists)
-- Password for hash below: ChangeMe123!
-- -----------------------------------------------------------------------------
insert into public.users (name, email, password_hash, role, is_active, invited_at, created_by)
values (
  'Super Admin',
  'superadmin@example.com',
  '$2b$12$Vv2uZnsw.2fBsJvVqv2I0uICdd3nW7qcKJSbUsYGRcPXm9R4CAFMe', //
  'super_admin',
  true,
  now(),
  null
)
on conflict (email) do nothing;