-- ============================================================================
-- SAG Manager — 0005 indexes + Row Level Security
-- ============================================================================
-- Cross-cutting performance indexes and RLS policies.
--
-- POLICY MODEL (single-tenant for now):
--   The SAG Manager is a single-operator product today (Glenn). Until a
--   multi-user model is fleshed out, RLS is defensive — any authenticated
--   `auth.uid()` can read + write app-level tables. This is safer than
--   "anon all-access" while still letting the Next.js app function with
--   the anon key once a user is signed in. The CHECK in each policy is
--   intentionally narrow: `auth.uid() is not null`.
--
--   When multi-tenant support lands, swap the policy bodies to scope rows
--   by an `owner_user_id` column or via an entity_grants join table.
-- ============================================================================

-- ─────────────────────────────────────────────────────────────────────────
-- High-cardinality lookup indexes — already covered in 0001-0004 for the
-- common cases, but extras for the heavy queries are added here so they
-- can be tuned independently without touching the table-creation migrations.
-- ─────────────────────────────────────────────────────────────────────────

-- Bills: (entity_slug, due_date) is the page's main query
create index if not exists bills_entity_due_date_idx on public.bills (entity_slug, due_date);
create index if not exists bills_due_status_idx on public.bills (due_date, status);

-- NCDOR: (entity_slug, period_year, period_month) for the period filter
create index if not exists ncdor_filings_period_idx
  on public.ncdor_filings (entity_slug, period_year desc, period_month desc);

-- License expiries — global "expiring soon" query
create index if not exists licenses_expires_status_idx
  on public.licenses (expires_date, status);
create index if not exists hemp_licenses_expires_status_idx
  on public.hemp_licenses (expires_date);
create index if not exists abc_permits_expires_status_idx
  on public.abc_permits (expires_date);
create index if not exists domains_expiration_status_idx
  on public.domains (expiration_date, status);

-- Subscriptions: monthly burn rollup by entity
create index if not exists subscriptions_entity_amount_idx
  on public.subscriptions (entity_slug, monthly_amount);

-- Owner draws: monthly / yearly totals (date prefix queries)
create index if not exists owner_draws_date_entity_idx
  on public.owner_draws (draw_date desc, from_entity_slug);

-- Social posts: scheduled-at window query
create index if not exists social_posts_scheduled_status_idx
  on public.social_posts (scheduled_at, status);

-- Gmail threads: classification join
create index if not exists gmail_threads_internal_date_message_count_idx
  on public.gmail_threads (internal_date desc, message_count);

-- ─────────────────────────────────────────────────────────────────────────
-- Enable RLS on every app table
-- ─────────────────────────────────────────────────────────────────────────
alter table public.entities                       enable row level security;
alter table public.entity_overrides               enable row level security;
alter table public.employees                      enable row level security;

alter table public.bills                          enable row level security;
alter table public.plaid_accounts                 enable row level security;
alter table public.plaid_access_tokens            enable row level security;
alter table public.plaid_txn_overrides            enable row level security;
alter table public.plaid_txn_splits               enable row level security;
alter table public.subscriptions                  enable row level security;
alter table public.subscription_ignored           enable row level security;
alter table public.owner_draws                    enable row level security;
alter table public.toast_imports                  enable row level security;
alter table public.toast_labor                    enable row level security;

alter table public.ncdor_filings                  enable row level security;
alter table public.hemp_licenses                  enable row level security;
alter table public.abc_permits                    enable row level security;
alter table public.ttb_step_status                enable row level security;
alter table public.nc_sos_filings                 enable row level security;
alter table public.licenses                       enable row level security;
alter table public.domains                        enable row level security;
alter table public.hires                          enable row level security;
alter table public.pto_rules                      enable row level security;
alter table public.pto_requests                   enable row level security;
alter table public.pay_schedules                  enable row level security;
alter table public.offer_letters                  enable row level security;
alter table public.onboarding_tasks               enable row level security;
alter table public.onboarding_progress            enable row level security;
alter table public.comp_benchmarks                enable row level security;

alter table public.documents                      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.music_releases                 enable row level security;
alter table public.music_bookings                 enable row level security;
alter table public.music_contacts                 enable row level security;
alter table public.music_streaming                enable row level security;
alter table public.gmail_threads                  enable row level security;
alter table public.gmail_thread_classifications   enable row level security;
alter table public.mail_calendar_suggestions      enable row level security;
alter table public.agent_threads                  enable row level security;
alter table public.research_findings              enable row level security;

-- ─────────────────────────────────────────────────────────────────────────
-- Policies — single-user model. Any signed-in user can read + write.
-- Each policy is dropped + re-created so re-running the migration is safe.
-- ─────────────────────────────────────────────────────────────────────────

do $$
declare
  t text;
  tables text[] := array[
    'entities','entity_overrides','employees',
    'bills','plaid_accounts','plaid_access_tokens','plaid_txn_overrides','plaid_txn_splits',
    'subscriptions','subscription_ignored','owner_draws','toast_imports','toast_labor',
    'ncdor_filings','hemp_licenses','abc_permits','ttb_step_status','nc_sos_filings',
    'licenses','domains','hires','pto_rules','pto_requests','pay_schedules',
    'offer_letters','onboarding_tasks','onboarding_progress','comp_benchmarks',
    'documents','social_accounts','social_posts',
    'music_releases','music_bookings','music_contacts','music_streaming',
    'gmail_threads','gmail_thread_classifications','mail_calendar_suggestions',
    'agent_threads','research_findings'
  ];
begin
  foreach t in array tables loop
    execute format(
      'drop policy if exists "Authenticated full access" on public.%I;',
      t
    );
    execute format(
      'create policy "Authenticated full access" on public.%I
         for all
         to authenticated
         using (auth.uid() is not null)
         with check (auth.uid() is not null);',
      t
    );
  end loop;
end $$;
