-- ============================================================================
-- SAG Manager — 0001 core entities
-- ============================================================================
-- Tables modeled after src/lib/sag/types.ts (Organization, ParentRelationship,
-- CmsInfo, etc.). One row per entry in ALL_ORGANIZATIONS (SAG parent,
-- subsidiaries, owner-investments, external-employers).
--
-- - `entities`           – authoritative entity records (mirrors Organization)
-- - `entity_overrides`   – local user overrides keyed by entity slug
-- - `employees`          – HR directory (mirrors Employee in employees-seed.ts)
--
-- Idempotent: `create extension if not exists`, `create table if not exists`,
-- and triggers / policies guarded with `drop if exists` before re-create.
-- ============================================================================

create extension if not exists "pgcrypto";

-- ─────────────────────────────────────────────────────────────────────────
-- Shared helpers
-- ─────────────────────────────────────────────────────────────────────────

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

-- ─────────────────────────────────────────────────────────────────────────
-- entities — one row per Organization
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.entities (
  id                   uuid primary key default gen_random_uuid(),
  slug                 text not null unique,
  name                 text not null,
  dba                  text,
  tagline              text,
  blurb                text,
  industry             text not null,
  entity_type          text not null,
  status               text not null default 'Active',
  formation_date       timestamptz,
  ein                  text,                 -- store full EIN here; UI redacts to last-4
  nc_sos_file_number   text,
  state                text not null default 'NC',
  parent_slug          text,                 -- self-ref by slug (matches Organization.parentId pattern via slug lookup)
  website              text,
  toast_restaurant_guid text,
  social_handles       jsonb not null default '{}'::jsonb,
  primary_color        text,
  emoji                text,
  cms                  jsonb,                -- CmsInfo (platform, websiteUrl, adminUrl, notes)
  location             text,
  parent_relationship  text,                 -- subsidiary | joint-venture | owner-investment | managed-artist | external-employer
  ownership_pct        numeric(6,3),
  co_owners            text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists entities_industry_idx on public.entities (industry);
create index if not exists entities_parent_slug_idx on public.entities (parent_slug);
create index if not exists entities_status_idx on public.entities (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- entity_overrides — local edits layered on seeded entity rows
-- Mirrors EntityOverride in src/components/app/entity-editor.tsx — UI lets
-- the operator override EIN / NC SoS file # / formation date / website /
-- social handles / freeform notes without touching the seed.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.entity_overrides (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null unique,    -- one override row per entity
  ein                  text,
  nc_sos_file_number   text,
  formation_date       timestamptz,
  website              text,
  instagram            text,
  facebook             text,
  tiktok               text,
  x_handle             text,
  notes                text,
  -- catch-all for any future status / industry / parent-relationship overrides
  extra                jsonb not null default '{}'::jsonb,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists entity_overrides_slug_idx on public.entity_overrides (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- employees — HR directory (mirrors Employee in employees-seed.ts)
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.employees (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  full_name            text not null,
  email                text,
  phone                text,
  role                 text not null,
  comp_type            text check (comp_type in ('salary','hourly','commission','equity','other')),
  comp_amount          numeric(14,2),
  start_date           timestamptz,
  end_date             timestamptz,
  status               text not null default 'Active'
    check (status in ('Active','On Leave','Terminated')),
  notes                text,
  onboarding           jsonb not null default '{}'::jsonb,  -- Record<itemId, boolean>
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists employees_entity_slug_idx on public.employees (entity_slug);
create index if not exists employees_status_idx on public.employees (status);

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