-- ═══════════════════════════════════════════════════════════════════════════
-- admin-api / tenant-api database + grant isolation setup
-- ═══════════════════════════════════════════════════════════════════════════
-- Run this once, as MySQL root (or an equivalent privileged account), AFTER
-- admin-api's migrations have created its tables (`npm run migrate` in
-- admin-api, using ADMIN_DB_MIGRATE_USER — a privileged account, never
-- admin_api_user itself, which has no DDL grants at all).
--
-- This is the enforcement point the architecture requires: the isolation
-- between admin_console and tenant_platform, and the immutability of
-- admin_audit_log, must hold even if application code has a bug — so both
-- are enforced here at the grant level, not only in admin-api's routes.
--
-- IMPORTANT — MySQL grants are additive, not restrictive: a database-level
-- GRANT ... ON admin_console.* always wins over any narrower table-level
-- REVOKE on one table within it. That means audit-log immutability can only
-- be achieved by granting DML per-table (never a blanket admin_console.*
-- grant) and giving admin_audit_log itself only SELECT+INSERT. The list
-- below must be kept in sync with admin-api/db/migrations/ — add a line
-- here whenever a migration adds a new table.
--
-- MVP note: this repo currently runs tenant-api's database as `lpms_dev`
-- (see backend/db/knexfile.js) rather than a database literally named
-- `tenant_platform` — adjust the REVOKE block below if you rename it. Both
-- databases living on the same MySQL server for now is the documented
-- cost-saving shortcut; nothing below assumes that stays true — splitting
-- them onto separate servers later is a connection-string change only.
--
-- Usage:
--   mysql -u root -p < admin-api/db/setup-grants.sql
-- ═══════════════════════════════════════════════════════════════════════════

-- ── 1. Create the admin_console database ────────────────────────────────────
CREATE DATABASE IF NOT EXISTS admin_console
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;



-- ── 3. Per-table grants — full DML everywhere except admin_audit_log ───────
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_roles             TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_permissions       TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_role_permissions  TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_users             TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_user_mfa         TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_sessions          TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_ip_allowlist      TO 'admin_api_user'@'%';

-- Phase 2 (tenant lifecycle) added no new tables to admin_console — everything
-- lives in tenant-api's own database, reached only via /internal/* calls.

-- Phase 3 (billing/payment config)
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_plans                TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_subscriptions        TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.billing_subscription_invoices TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_gateway_credentials  TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_routing_rules        TO 'admin_api_user'@'%';
-- Payment Orchestration Service (Law Firm -> JurisLink subscription payments)
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.payment_transactions         TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.refund_requests              TO 'admin_api_user'@'%';

-- Phase 4 (monitoring, support, system-wide issue resolution). Note:
-- maintenance_windows/platform_kill_switches/job_run_log live in tenant-api's
-- lpms_dev, NOT here — they're read by backend's own enforcement middleware
-- on every tenant request, so they must be data backend can query directly.
-- No new grants needed there: tenant_api_user already has a schema-level
-- DML grant on lpms_dev.* (see §4 below), which covers new tables automatically.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.service_health_checks TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.security_alerts      TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.support_tickets      TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.kb_articles          TO 'admin_api_user'@'%';
-- Storefront marketing blog (docs/SPEC.md §33.9, migration 026) — read by
-- both the authenticated admin-web CRUD surface and the unauthenticated
-- public endpoint (routes/publicRoutes.js), both same DB user.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.blog_posts           TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.deployments          TO 'admin_api_user'@'%';

-- Phase 5 (compliance/Act 843, analytics, backup/DR tracking, integration
-- registry). Same note as Phase 4 for api_keys/platform_announcements —
-- those live in lpms_dev (backend migration 155), not here, since backend
-- must check them on incoming requests.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.dsar_requests             TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.breach_incidents          TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.backup_records            TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.restore_tests             TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.third_party_integrations  TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tax_currency_config          TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.webhook_deliveries           TO 'admin_api_user'@'%';

-- Phase 6 (module 13 — in-app notifications; foundation for modules 14/15).
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_notifications TO 'admin_api_user'@'%';

-- Phase 6 (module 14 — SMS/email alerting + escalation).
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_channel_config    TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_escalation_rules  TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_escalation_chain  TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.alert_delivery_log      TO 'admin_api_user'@'%';

-- Phase 6 (module 16 — per-tenant resource usage reporting).
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tenant_usage_rollups    TO 'admin_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.tenant_api_call_rollups TO 'admin_api_user'@'%';

-- Phase 6 (module 15 — threat detection thresholds; the alerts themselves
-- reuse the existing security_alerts table/grant from Phase 4).
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.threat_detection_config TO 'admin_api_user'@'%';

-- Plan Builder (docs/AUDIT.md, deferred item #5 follow-up) — per-feature
-- entitlement matrix per plan tier, edited via admin-web's new Plan Builder
-- UI and pushed to the tenant backend's plan_tier_entitlements table on save.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.plan_entitlements TO 'admin_api_user'@'%';

-- §17.3 sales-assisted onboarding ("Contact Sales") request queue — public
-- submission via /api/v1/public/signup-requests, reviewed in admin-web's new
-- Sales Requests page.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.platform_signup_requests TO 'admin_api_user'@'%';

-- Phase 7 (module 7 — break-glass credential recovery). The runtime app only
-- ever SELECTs a candidate token and UPDATEs it to used_at; INSERT exists so
-- scripts/issue-recovery-token.js (which connects as this same scoped user,
-- not the privileged migration user) can mint new tokens.
GRANT SELECT, INSERT, UPDATE, DELETE ON admin_console.admin_recovery_tokens TO 'admin_api_user'@'%';

-- Audit-log immutability: SELECT + INSERT only, ever. No route in this
-- codebase issues UPDATE/DELETE against this table — this grant means MySQL
-- itself would reject it even if one did.
GRANT SELECT, INSERT ON admin_console.admin_audit_log TO 'admin_api_user'@'%';

-- Explicit belt-and-suspenders REVOKE — makes the tenant-isolation boundary
-- visible in this file even though a fresh user has no grants elsewhere by
-- default. Replace `lpms_dev` with your tenant-api database name if renamed.
REVOKE ALL PRIVILEGES ON lpms_dev.* FROM 'admin_api_user'@'%';


GRANT SELECT, INSERT, UPDATE, DELETE ON lpms_dev.*  TO 'tenant_api_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON lpms_test.* TO 'tenant_api_user'@'%';
REVOKE ALL PRIVILEGES ON admin_console.* FROM 'tenant_api_user'@'%';
REVOKE ALL PRIVILEGES ON admin_console_test.* FROM 'tenant_api_user'@'%';

FLUSH PRIVILEGES;

-- ── 5. Verify the boundary ───────────────────────────────────────────────
-- SHOW GRANTS FOR 'admin_api_user'@'%';
-- Expected output: per-table GRANTs on `admin_console` tables only, with
-- admin_audit_log limited to SELECT, INSERT — and nothing mentioning
-- lpms_dev/tenant_platform at all. admin-api/tests/db-grant-boundary.test.js
-- automates this check against a live connection.
