-- ============================================================================
-- SAG Manager — 0003 compliance + HR
-- ============================================================================
-- Compliance: NCDOR (C3), Hemp licenses (C4), ABC permits (C5), TTB step
-- status (C6, parameterized by entity_slug so future TTB entities reuse the
-- same table), NC SoS filings (C1+C2), general licenses, domain portfolio.
--
-- HR: hires (hiring board), PTO (rules + requests), pay schedules, offer
-- letters (AI6), onboarding tasks + per-hire progress (H1), comp benchmarks
-- (H5). `employees` lives in 0001 since it's referenced cross-domain.
-- ============================================================================

create extension if not exists "pgcrypto";

-- ─────────────────────────────────────────────────────────────────────────
-- ncdor_filings — C3 NC sales/use (E-500) per entity per period
-- Mirrors NcdorFiling in ncdor-tracker.tsx.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.ncdor_filings (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  period_year          integer not null,
  period_month         integer not null check (period_month between 1 and 12),
  gross_sales          numeric(14,2),
  taxable_sales        numeric(14,2),
  tax_owed             numeric(14,2),
  status               text not null default 'Unfiled'
    check (status in ('Unfiled','Drafted','Submitted','Paid','Late')),
  filed_date           timestamptz,
  confirmation_number  text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),
  unique (entity_slug, period_year, period_month)
);

create index if not exists ncdor_filings_entity_period_idx
  on public.ncdor_filings (entity_slug, period_year, period_month);
create index if not exists ncdor_filings_status_idx on public.ncdor_filings (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- hemp_licenses — C4 NC Industrial Hemp / Hemp Retailer
-- Mirrors HempLicense in hemp-license-tracker.tsx.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.hemp_licenses (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  license_type         text not null,
  license_number       text not null,
  issued_date          timestamptz,
  expires_date         timestamptz,
  growing_locations    text,
  thc_test_schedule    text,
  thc_test_notes       text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists hemp_licenses_entity_idx on public.hemp_licenses (entity_slug);
create index if not exists hemp_licenses_expires_idx on public.hemp_licenses (expires_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- abc_permits — C5 NC ABC permits
-- Mirrors AbcPermit in abc-permit-tracker.tsx.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.abc_permits (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  permit_type          text not null,
  permit_number        text not null,
  issued_date          timestamptz,
  expires_date         timestamptz,
  premises_address     text,
  abc_board_contact    text,
  renewal_notes        text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists abc_permits_entity_idx on public.abc_permits (entity_slug);
create index if not exists abc_permits_expires_idx on public.abc_permits (expires_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- ttb_step_status — C6 TTB checklist progress, parameterized per entity
-- Originally scoped to Koshu; the entity_slug column lets future TTB
-- entities (e.g. a distillery) reuse the same table. Step definitions
-- (id, name, agency, form, description, estimated_duration_days) come
-- from KOSHU_TTB_STEPS / future seeds; rows here track per-entity status.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.ttb_step_status (
  id                       uuid primary key default gen_random_uuid(),
  entity_slug              text not null,
  step_number              integer not null,
  step_name                text not null,
  agency                   text,
  form                     text,
  description              text,
  status                   text not null default 'Not Started'
    check (status in ('Not Started','In Progress','Submitted','Approved','Blocked')),
  estimated_duration_days  integer,
  completed_date           timestamptz,
  notes                    text,
  created_at               timestamptz not null default now(),
  updated_at               timestamptz not null default now(),
  unique (entity_slug, step_number)
);

create index if not exists ttb_step_status_entity_idx on public.ttb_step_status (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- nc_sos_filings — C1 + C2 NC Secretary of State filings (Annual Report etc.)
-- Mirrors SeedFiling + the FilingState overrides in filings-tracker.tsx.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.nc_sos_filings (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  filing_type          text not null,
  agency               text not null,
  cadence              text,
  next_due_date        timestamptz,
  last_filed_date      timestamptz,
  status               text not null default 'Upcoming'
    check (status in ('Upcoming','Filed','Late','Not Required')),
  filing_url           text,
  description          text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists nc_sos_filings_entity_due_idx
  on public.nc_sos_filings (entity_slug, next_due_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- licenses — general license registry (KnownLicense + user-added)
-- Mirrors KnownLicense in licenses-seed.ts. `source` distinguishes seeded
-- rows from user additions (preserves the seed/user split from the UI).
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.licenses (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  license_type         text not null,
  agency               text not null,
  license_number       text,
  issued_date          timestamptz,
  expires_date         timestamptz,
  status               text not null default 'Active'
    check (status in ('Active','Expired','Pending','In Application','Renewing')),
  cadence              text,
  source               text not null default 'user'
    check (source in ('seed','user')),
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists licenses_entity_idx on public.licenses (entity_slug);
create index if not exists licenses_expires_idx on public.licenses (expires_date);

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

-- ─────────────────────────────────────────────────────────────────────────
-- domains — domain portfolio (DomainEntry + per-domain overrides)
-- Single table — overrides applied inline rather than via a join table.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.domains (
  domain               text primary key,              -- "1ncblockchain.com"
  category             text,
  expiration_date      timestamptz,
  est_value            numeric(14,2),
  status               text,
  business_use         text,
  registrar_url        text,
  renewed_at           timestamptz,
  notes                text,
  source               text not null default 'seed'
    check (source in ('seed','user')),
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists domains_expiration_idx on public.domains (expiration_date);
create index if not exists domains_category_idx on public.domains (category);

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

-- ============================================================================
-- HR — hires, PTO, pay, offers, onboarding, comp
-- ============================================================================

-- ─────────────────────────────────────────────────────────────────────────
-- hires — open + filled job openings (hiring-board.tsx JobOpening)
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.hires (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  title                text not null,
  employment_type      text not null
    check (employment_type in ('Full-time','Part-time','Contract','Internship','Temporary')),
  status               text not null default 'Draft'
    check (status in ('Draft','Posted','Interviewing','Filled','Cancelled')),
  location             text,
  comp_min             numeric(14,2),
  comp_max             numeric(14,2),
  comp_type            text check (comp_type in ('salary','hourly')),
  description          text,
  qualifications       text,
  indeed_draft         text,
  posted_date          timestamptz,
  filled_date          timestamptz,
  applicant_count      integer not null default 0,
  indeed_job_id        text,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists hires_entity_idx on public.hires (entity_slug);
create index if not exists hires_status_idx on public.hires (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- pto_rules — per-entity PTO accrual overrides (H2)
-- Mirrors PtoRule in pto.ts. Seeded defaults live in code; this table
-- carries only the operator-set overrides (one row per overridden entity).
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.pto_rules (
  entity_slug          text primary key,
  hours_per_year       numeric(8,2) not null,
  rollover_cap_hours   numeric(8,2) not null,
  waiting_period_days  integer not null default 0,
  policy               text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

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

-- ─────────────────────────────────────────────────────────────────────────
-- pto_requests — H2 PTO request log
-- Mirrors PtoRequest in pto.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.pto_requests (
  id                   uuid primary key default gen_random_uuid(),
  employee_id          uuid not null,
  start_date           timestamptz not null,
  end_date             timestamptz not null,
  hours_requested      numeric(8,2) not null,
  reason               text,
  status               text not null default 'pending'
    check (status in ('pending','approved','denied','cancelled')),
  submitted_at         timestamptz not null default now(),
  decided_at           timestamptz,
  decided_by           text,
  decision_note        text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists pto_requests_employee_idx on public.pto_requests (employee_id);
create index if not exists pto_requests_status_idx on public.pto_requests (status);

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

-- ─────────────────────────────────────────────────────────────────────────
-- pay_schedules — H3 per-entity pay frequency overrides
-- Mirrors PayPeriodSchedule in payroll-prep.ts.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.pay_schedules (
  entity_slug          text primary key,
  frequency            text not null
    check (frequency in ('weekly','biweekly','semimonthly','monthly')),
  anchor_date          timestamptz not null,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

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

-- ─────────────────────────────────────────────────────────────────────────
-- offer_letters — AI6 / H4 generated offer letters saved to docs
-- Mirrors OfferLetter + OfferLetterVaultDoc in save-offer-letter.ts.
-- The markdown body lives in `body`; the docs vault references this row
-- via the shared id pattern (or this row references a documents row).
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.offer_letters (
  id                   uuid primary key default gen_random_uuid(),
  entity_slug          text not null,
  candidate_name       text not null,
  role                 text,
  title                text,
  salary               numeric(14,2),
  start_date           timestamptz,
  body                 text not null,                 -- markdown
  agent_model          text,
  generated_at         timestamptz not null default now(),
  signed               boolean not null default false,
  signed_at            timestamptz,
  document_id          uuid,                          -- optional ref to documents row
  metadata             jsonb not null default '{}'::jsonb,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists offer_letters_entity_idx on public.offer_letters (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- onboarding_tasks — H1 catalog of onboarding task definitions
-- Seed list is in employees-seed.ONBOARDING_ITEMS. This table makes the
-- catalog editable per-entity if needed.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.onboarding_tasks (
  id                   text primary key,              -- "w4", "i9", etc.
  label                text not null,
  category             text,
  entity_slug          text,                          -- null = applies to all entities
  required             boolean not null default true,
  sort_order           integer not null default 0,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

create index if not exists onboarding_tasks_entity_idx on public.onboarding_tasks (entity_slug);

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

-- ─────────────────────────────────────────────────────────────────────────
-- onboarding_progress — H1 per-hire task completion
-- One row per (employee, task). Mirrors the `Employee.onboarding` map.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.onboarding_progress (
  id                   uuid primary key default gen_random_uuid(),
  employee_id          uuid not null,
  task_id              text not null,
  completed            boolean not null default false,
  completed_at         timestamptz,
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),
  unique (employee_id, task_id)
);

create index if not exists onboarding_progress_employee_idx
  on public.onboarding_progress (employee_id);

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

-- ─────────────────────────────────────────────────────────────────────────
-- comp_benchmarks — H5 NC market reference rates
-- Mirrors MarketRate in comp-benchmarks.ts. Seeded but editable so the
-- operator can refresh figures as new survey data lands.
-- ─────────────────────────────────────────────────────────────────────────
create table if not exists public.comp_benchmarks (
  id                   uuid primary key default gen_random_uuid(),
  role                 text not null,
  aliases              jsonb not null default '[]'::jsonb,    -- string[]
  pay_type             text not null check (pay_type in ('hourly','salary')),
  region               text not null default 'NC',
  p25                  numeric(14,2) not null,
  p50                  numeric(14,2) not null,
  p75                  numeric(14,2) not null,
  source               text not null,
  as_of                text not null,                 -- YYYY-MM
  notes                text,
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now(),
  unique (role, region)
);

create index if not exists comp_benchmarks_role_idx on public.comp_benchmarks (role);

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