-- South Armz Global Corporate Manager — Phase 0 schema
-- Run this on the Supabase project once provisioned.

create extension if not exists "uuid-ossp";
create extension if not exists "vector";  -- pgvector for document embeddings (Phase 7)

-- ============================================================================
-- ORGANIZATIONS (parent + subsidiaries)
-- ============================================================================
create table public.organizations (
  id              uuid primary key default uuid_generate_v4(),
  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  date,
  ein_last4       text,
  nc_sos_file_number text,
  state           text not null default 'NC',
  parent_id       uuid references public.organizations(id) on delete set null,
  website         text,
  toast_restaurant_guid text,
  social_handles  jsonb default '{}'::jsonb,
  primary_color   text,
  emoji           text,
  created_at      timestamptz not null default now(),
  updated_at      timestamptz not null default now()
);
create index on public.organizations (parent_id);
create index on public.organizations (industry);

-- ============================================================================
-- USERS — Supabase auth.users is the source; this table layers app metadata
-- ============================================================================
create table public.profiles (
  id          uuid primary key references auth.users(id) on delete cascade,
  full_name   text,
  role        text not null default 'employee'  -- super_admin | entity_admin | employee | backer | cpa
    check (role in ('super_admin','entity_admin','employee','backer','cpa')),
  created_at  timestamptz not null default now()
);

-- Grants: which entities does a non-super-admin user manage?
create table public.entity_grants (
  user_id         uuid not null references auth.users(id) on delete cascade,
  organization_id uuid not null references public.organizations(id) on delete cascade,
  role            text not null default 'entity_admin'
    check (role in ('entity_admin','employee','viewer')),
  created_at      timestamptz not null default now(),
  primary key (user_id, organization_id)
);

-- ============================================================================
-- BANKING (Plaid)
-- ============================================================================
create table public.bank_accounts (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  plaid_item_id   text,
  plaid_account_id text not null unique,
  institution     text,
  account_name    text,
  account_type    text,
  mask            text,
  balance_cache   numeric(14,2),
  balance_updated_at timestamptz,
  active          boolean not null default true,
  created_at      timestamptz not null default now()
);
create index on public.bank_accounts (organization_id);

create table public.transactions (
  id                   uuid primary key default uuid_generate_v4(),
  bank_account_id      uuid not null references public.bank_accounts(id) on delete cascade,
  organization_id      uuid not null references public.organizations(id) on delete cascade,
  plaid_transaction_id text unique,
  date                 date not null,
  amount               numeric(14,2) not null,
  merchant_name        text,
  category             text,
  subcategory          text,
  pending              boolean not null default false,
  manual_category      text,
  notes                text,
  created_at           timestamptz not null default now()
);
create index on public.transactions (organization_id, date desc);
create index on public.transactions (merchant_name);

-- ============================================================================
-- TOAST POS
-- ============================================================================
create table public.toast_locations (
  id                    uuid primary key default uuid_generate_v4(),
  organization_id       uuid not null references public.organizations(id) on delete cascade,
  toast_restaurant_guid text unique,
  toast_management_group text,
  display_name          text
);

create table public.toast_sales_daily (
  id              uuid primary key default uuid_generate_v4(),
  toast_location_id uuid references public.toast_locations(id) on delete cascade,
  organization_id uuid not null references public.organizations(id) on delete cascade,
  date            date not null,
  gross_sales     numeric(14,2) not null default 0,
  net_sales       numeric(14,2) not null default 0,
  discount_amount numeric(14,2) not null default 0,
  tax_amount      numeric(14,2) not null default 0,
  item_qty        integer not null default 0,
  labor_hours     numeric(8,2) not null default 0,
  labor_cost      numeric(14,2) not null default 0,
  raw_payload     jsonb,
  unique (organization_id, date)
);
create index on public.toast_sales_daily (organization_id, date desc);

-- ============================================================================
-- COMPLIANCE & LICENSING
-- ============================================================================
create table public.compliance_filings (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  filing_type     text not null,  -- 'NC SoS Annual Report' | 'NCDOR E-500' | 'Hemp License Renewal' | etc.
  agency          text not null,
  due_date        date not null,
  filed_date      date,
  status          text not null default 'Upcoming'
    check (status in ('Upcoming','Filed','Late','Not Required')),
  document_url    text,
  notes           text,
  created_at      timestamptz not null default now()
);
create index on public.compliance_filings (organization_id, due_date);

create table public.licenses (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  license_type    text not null,
  agency          text not null,
  license_number  text,
  issued_date     date,
  expires_date    date,
  status          text not null default 'Active'
    check (status in ('Active','Expired','Pending','In Application')),
  renewal_in_progress boolean not null default false,
  notes           text
);
create index on public.licenses (organization_id, expires_date);

create table public.ttb_application_steps (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  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,
  due_date        date,
  completed_date  date,
  document_urls   jsonb default '[]'::jsonb,
  notes           text,
  unique (organization_id, step_number)
);

-- ============================================================================
-- BILLS
-- ============================================================================
create table public.bills (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  vendor          text not null,
  amount          numeric(14,2) not null,
  due_date        date not null,
  status          text not null default 'Unscheduled'
    check (status in ('Unscheduled','Scheduled','Paid','Overdue')),
  recurring       boolean not null default false,
  recurrence_pattern text,
  paid_via_bank_account_id uuid references public.bank_accounts(id) on delete set null,
  document_url    text,
  notes           text,
  created_at      timestamptz not null default now()
);
create index on public.bills (organization_id, due_date);

create table public.subscriptions (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  merchant_name   text not null,
  amount          numeric(14,2) not null,
  cadence         text not null,  -- 'monthly' | 'annual' | 'quarterly'
  first_seen_date date,
  last_charge_date date,
  cancellation_url text,
  status          text not null default 'Active'
    check (status in ('Active','Cancelled','Paused'))
);

-- ============================================================================
-- HR
-- ============================================================================
create table public.employees (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  user_id         uuid references auth.users(id) on delete set null,
  full_name       text not null,
  email           text,
  role            text,
  comp_type       text check (comp_type in ('salary','hourly','commission','equity','other')),
  comp_amount     numeric(12,2),
  start_date      date,
  end_date        date,
  status          text not null default 'Active'
    check (status in ('Active','On Leave','Terminated'))
);
create index on public.employees (organization_id);

-- ============================================================================
-- SOCIAL MEDIA
-- ============================================================================
create table public.social_accounts (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid not null references public.organizations(id) on delete cascade,
  platform        text not null,  -- 'instagram' | 'facebook' | 'tiktok' | 'x' | 'youtube' | 'linkedin' | 'threads'
  handle          text not null,
  oauth_token_encrypted text,
  follower_count_cache integer,
  unique (organization_id, platform)
);

create table public.social_posts (
  id                  uuid primary key default uuid_generate_v4(),
  organization_id     uuid not null references public.organizations(id) on delete cascade,
  social_account_id   uuid references public.social_accounts(id) on delete set null,
  content             text not null,
  media_urls          jsonb default '[]'::jsonb,
  scheduled_at        timestamptz,
  posted_at           timestamptz,
  status              text not null default 'Draft'
    check (status in ('Draft','Scheduled','Posted','Failed','Cancelled')),
  performance         jsonb default '{}'::jsonb,
  created_at          timestamptz not null default now()
);
create index on public.social_posts (organization_id, scheduled_at);

-- ============================================================================
-- CROWDFUND (Reg CF)
-- ============================================================================
create table public.crowdfund_projects (
  id                   uuid primary key default uuid_generate_v4(),
  organization_id      uuid not null references public.organizations(id) on delete cascade,
  slug                 text not null unique,
  title                text not null,
  story_md             text,
  video_url            text,
  hero_image_url       text,
  goal_amount          numeric(14,2) not null,
  raised_amount        numeric(14,2) not null default 0,
  deadline             date,
  status               text not null default 'Draft'
    check (status in ('Draft','Live','Funded','Failed','Cancelled')),
  reg_cf_form_c_url    text,
  accredited_only      boolean not null default false,
  intermediary_partner text,  -- 'Wefunder' | 'Republic' | 'StartEngine' | 'Self-Hosted'
  created_at           timestamptz not null default now()
);

create table public.crowdfund_backers (
  id                   uuid primary key default uuid_generate_v4(),
  project_id           uuid not null references public.crowdfund_projects(id) on delete cascade,
  user_id              uuid references auth.users(id) on delete set null,
  amount               numeric(14,2) not null,
  perk                 text,
  stripe_payment_intent text,
  accreditation_status text check (accreditation_status in ('accredited','non_accredited','unknown')),
  status               text not null default 'Pending'
    check (status in ('Pending','Confirmed','Refunded','Cancelled')),
  backed_at            timestamptz not null default now()
);

-- ============================================================================
-- DOCUMENTS & WIKI
-- ============================================================================
create table public.documents (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid references public.organizations(id) on delete cascade,
  doc_type        text not null,
  name            text not null,
  storage_url     text not null,
  ai_summary      text,
  embedding       vector(1536),
  uploaded_at     timestamptz not null default now(),
  uploaded_by     uuid references auth.users(id) on delete set null
);
create index on public.documents (organization_id);

-- ============================================================================
-- AI AGENTS
-- ============================================================================
create table public.agent_threads (
  id              uuid primary key default uuid_generate_v4(),
  organization_id uuid references public.organizations(id) on delete set null,
  agent_type      text not null
    check (agent_type in ('cfo','compliance','secretary','marketing','hr')),
  user_id         uuid not null references auth.users(id) on delete cascade,
  title           text,
  created_at      timestamptz not null default now()
);

create table public.agent_messages (
  id          uuid primary key default uuid_generate_v4(),
  thread_id   uuid not null references public.agent_threads(id) on delete cascade,
  role        text not null check (role in ('user','assistant','system','tool')),
  content     text not null,
  tool_calls  jsonb,
  cached_blocks jsonb,
  created_at  timestamptz not null default now()
);
create index on public.agent_messages (thread_id, created_at);

-- ============================================================================
-- AUDIT LOG
-- ============================================================================
create table public.audit_log (
  id              uuid primary key default uuid_generate_v4(),
  user_id         uuid references auth.users(id) on delete set null,
  organization_id uuid references public.organizations(id) on delete set null,
  action          text not null,
  resource_type   text,
  resource_id     uuid,
  before_state    jsonb,
  after_state     jsonb,
  ip_address      text,
  created_at      timestamptz not null default now()
);
create index on public.audit_log (created_at desc);

-- ============================================================================
-- RLS POLICIES — default-deny, grant on entity_grants
-- ============================================================================
alter table public.organizations enable row level security;
alter table public.bank_accounts enable row level security;
alter table public.transactions enable row level security;
alter table public.toast_locations enable row level security;
alter table public.toast_sales_daily enable row level security;
alter table public.compliance_filings enable row level security;
alter table public.licenses enable row level security;
alter table public.ttb_application_steps enable row level security;
alter table public.bills enable row level security;
alter table public.subscriptions enable row level security;
alter table public.employees enable row level security;
alter table public.social_accounts enable row level security;
alter table public.social_posts enable row level security;
alter table public.crowdfund_projects enable row level security;
alter table public.crowdfund_backers enable row level security;
alter table public.documents enable row level security;
alter table public.agent_threads enable row level security;
alter table public.agent_messages enable row level security;
alter table public.audit_log enable row level security;
alter table public.profiles enable row level security;
alter table public.entity_grants enable row level security;

-- Helper function: is user super_admin?
create or replace function public.is_super_admin()
returns boolean
language sql
stable
security definer
as $$
  select exists (
    select 1 from public.profiles
    where id = auth.uid() and role = 'super_admin'
  );
$$;

-- Helper: does user have access to organization?
create or replace function public.has_org_access(org_id uuid)
returns boolean
language sql
stable
security definer
as $$
  select public.is_super_admin()
    or exists (
      select 1 from public.entity_grants
      where user_id = auth.uid() and organization_id = org_id
    );
$$;

-- Public read on organizations (for the public portfolio page)
create policy "Public can read active organizations"
  on public.organizations for select
  using (status = 'Active' or public.is_super_admin());

-- Crowdfund projects are publicly readable when live
create policy "Public can read live crowdfund projects"
  on public.crowdfund_projects for select
  using (status = 'Live' or status = 'Funded' or public.has_org_access(organization_id));

-- Default policy for org-scoped tables: only users with grant can see
create policy "Users see only their granted entities" on public.bank_accounts
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.transactions
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.toast_sales_daily
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.compliance_filings
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.licenses
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.ttb_application_steps
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.bills
  for all using (public.has_org_access(organization_id));
create policy "Users see only their granted entities" on public.employees
  for all using (public.has_org_access(organization_id));

-- Profiles: users see their own
create policy "Users see their own profile" on public.profiles
  for select using (id = auth.uid() or public.is_super_admin());
create policy "Users update their own profile" on public.profiles
  for update using (id = auth.uid());

-- Audit log: super admin only
create policy "Super admin reads audit log" on public.audit_log
  for select using (public.is_super_admin());

-- Trigger: auto-create profile on user signup
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
as $$
begin
  insert into public.profiles (id, full_name)
  values (new.id, new.raw_user_meta_data->>'full_name');
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

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

create trigger touch_organizations_updated_at
  before update on public.organizations
  for each row execute function public.touch_updated_at();