-- Tenant structure
{
  "id": "uuid",
  "slug": "acme",                    -- URL-friendly identifier
  "name": "Acme Corporation",        -- Display name
  "status": "active",                -- active | suspended | deleted
  "plan_id": "pro",                  -- Subscription plan
  "owner_user_id": "uuid",           -- Primary owner
  "max_users": 50,                   -- User limit
  "max_storage_gb": 100,             -- Storage quota
  "max_api_requests_per_month": 100000,
  "settings": {},                    -- Custom settings (JSONB)
  "metadata": {}                     -- Additional metadata
}

-- Every table has a tenant_id column
CREATE TABLE users (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL,
  email TEXT,
  ...
);

-- RLS policy ensures users only see their tenant's data
CREATE POLICY user_isolation ON users
  FOR ALL
  USING (tenant_id = tenants.current_tenant_id());

CREATE TABLE tenants.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',

    -- Resource Quotas
    plan_id TEXT DEFAULT 'free',
    max_users INTEGER DEFAULT 5,
    max_storage_gb INTEGER DEFAULT 1,
    max_api_requests_per_month INTEGER DEFAULT 10000,

    -- Metadata
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    suspended_at TIMESTAMPTZ,
    deleted_at TIMESTAMPTZ,

    -- Ownership
    owner_user_id UUID NOT NULL,

    -- Flexible configuration
    settings JSONB DEFAULT '{}'::jsonb,
    metadata JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE tenants.tenant_domains (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    domain TEXT UNIQUE NOT NULL,
    is_primary BOOLEAN DEFAULT false,
    is_verified BOOLEAN DEFAULT false,
    verification_token TEXT,
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE tenants.tenant_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    user_id UUID NOT NULL,
    role TEXT NOT NULL DEFAULT 'member',
    -- Roles: owner, admin, member, guest

    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    invited_by UUID,

    UNIQUE (tenant_id, user_id)
);

CREATE TABLE tenants.tenant_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,
    key TEXT NOT NULL,
    value JSONB NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (tenant_id, key)
);

-- Get current tenant ID from Hasura session variable
CREATE OR REPLACE FUNCTION tenants.current_tenant_id()
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('hasura.user.x-hasura-tenant-id', true)::uuid;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Get current user ID from session
CREATE OR REPLACE FUNCTION tenants.current_user_id()
RETURNS UUID AS $$
BEGIN
    RETURN current_setting('hasura.user.x-hasura-user-id', true)::uuid;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- Check if user is member of tenant
CREATE OR REPLACE FUNCTION tenants.is_tenant_member(p_tenant_id UUID, p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM tenants.tenant_members
        WHERE tenant_id = p_tenant_id
        AND user_id = p_user_id
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Get user's role in tenant
CREATE OR REPLACE FUNCTION tenants.get_user_tenant_role(p_tenant_id UUID, p_user_id UUID)
RETURNS TEXT AS $$
DECLARE
    v_role TEXT;
BEGIN
    SELECT role INTO v_role
    FROM tenants.tenant_members
    WHERE tenant_id = p_tenant_id
    AND user_id = p_user_id;

    RETURN v_role;
END;
$$ LANGUAGE plpgsql STABLE;

CREATE POLICY tenant_member_select ON tenants.tenants
    FOR SELECT
    USING (
        id = tenants.current_tenant_id()
        OR
        tenants.is_tenant_member(id, tenants.current_user_id())
    );

CREATE POLICY tenant_owner_update ON tenants.tenants
    FOR UPDATE
    USING (
        owner_user_id = tenants.current_user_id()
        OR
        tenants.get_user_tenant_role(id, tenants.current_user_id()) = 'owner'
    );

CREATE POLICY tenant_domains_select ON tenants.tenant_domains
    FOR SELECT
    USING (
        tenants.is_tenant_member(tenant_id, tenants.current_user_id())
    );

CREATE POLICY tenant_domains_manage ON tenants.tenant_domains
    FOR ALL
    USING (
        tenants.get_user_tenant_role(tenant_id, tenants.current_user_id()) IN ('owner', 'admin')
    );

-- This query returns NOTHING if current_tenant_id() != tenant_id
SELECT * FROM auth.users;

-- Even with malicious WHERE clause, RLS policy applies
SELECT * FROM auth.users WHERE 1=1 OR tenant_id != current_tenant_id();
-- Still filtered by: WHERE tenant_id = current_tenant_id()

-- Check storage quota
CREATE OR REPLACE FUNCTION tenants.check_storage_quota(p_tenant_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    v_current_size BIGINT;
    v_max_size BIGINT;
BEGIN
    v_current_size := tenants.get_tenant_database_size(p_tenant_id);

    SELECT max_storage_gb * 1073741824 INTO v_max_size
    FROM tenants.tenants
    WHERE id = p_tenant_id;

    RETURN v_current_size < v_max_size;
END;
$$ LANGUAGE plpgsql;

-- Check API request quota
CREATE OR REPLACE FUNCTION tenants.check_api_quota(p_tenant_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    v_current_count INTEGER;
    v_max_count INTEGER;
BEGIN
    v_current_count := tenants.get_tenant_api_requests(p_tenant_id);

    SELECT max_api_requests_per_month INTO v_max_count
    FROM tenants.tenants
    WHERE id = p_tenant_id;

    RETURN v_current_count < v_max_count;
END;
$$ LANGUAGE plpgsql;

-- Prevent user creation if tenant at limit
CREATE OR REPLACE FUNCTION tenants.check_user_limit()
RETURNS TRIGGER AS $$
DECLARE
    v_tenant_id UUID;
    v_max_users INTEGER;
    v_current_users INTEGER;
BEGIN
    v_tenant_id := NEW.tenant_id;

    SELECT max_users INTO v_max_users
    FROM tenants.tenants
    WHERE id = v_tenant_id;

    SELECT COUNT(*) INTO v_current_users
    FROM auth.users
    WHERE tenant_id = v_tenant_id;

    IF v_current_users >= v_max_users THEN
        RAISE EXCEPTION 'Tenant has reached maximum user limit (%)', v_max_users;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_user_limit
    BEFORE INSERT ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION tenants.check_user_limit();

SET hasura.user.x-hasura-tenant-id = '550e8400-e29b-41d4-a716-446655440000';

-- Enable RLS on table
ALTER TABLE auth.users ENABLE ROW LEVEL SECURITY;

-- Users can only see users in their tenant
CREATE POLICY user_tenant_isolation ON auth.users
    FOR SELECT
    USING (tenant_id = tenants.current_tenant_id());

-- Logs table with tenant_id
ALTER TABLE logs.log_entries ADD COLUMN tenant_id UUID;

-- RLS policy
CREATE POLICY logs_tenant_isolation ON logs.log_entries
    FOR ALL
    USING (tenant_id = tenants.current_tenant_id());

-- Metrics with tenant context
INSERT INTO metrics.metrics (tenant_id, metric_name, value)
VALUES (current_tenant_id(), 'api.request.count', 1);

-- Query tenant-specific metrics
SELECT * FROM metrics.metrics
WHERE tenant_id = current_tenant_id()
AND metric_name LIKE 'api.request%';

-- Distributed traces with tenant context
ALTER TABLE tracing.traces ADD COLUMN tenant_id UUID;

-- RLS isolation
CREATE POLICY traces_tenant_isolation ON tracing.traces
    FOR ALL
    USING (tenant_id = tenants.current_tenant_id());

-- Test: User in tenant A cannot see tenant B data
SET hasura.user.x-hasura-tenant-id = 'tenant-a-uuid';
SELECT COUNT(*) FROM auth.users WHERE tenant_id = 'tenant-b-uuid';
-- Must return: 0

-- Super admin role can switch tenants
CREATE POLICY admin_cross_tenant_access ON tenants.tenants
    FOR SELECT
    USING (
        tenants.is_tenant_member(id, tenants.current_user_id())
        OR
        tenants.current_user_role() = 'super_admin'
    );

-- Audit log table
CREATE TABLE audit.log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    user_id UUID NOT NULL,
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id UUID,
    changes JSONB,
    ip_address INET,
    user_agent TEXT,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Trigger for auditing
CREATE OR REPLACE FUNCTION audit.log_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit.log (tenant_id, user_id, action, resource_type, resource_id, changes)
    VALUES (
        tenants.current_tenant_id(),
        tenants.current_user_id(),
        TG_OP,
        TG_TABLE_NAME,
        NEW.id,
        jsonb_build_object('old', row_to_json(OLD), 'new', row_to_json(NEW))
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- PostgreSQL encryption
ALTER TABLE tenant_550e8400.patient_records
  SET (encrypted = true);

-- MinIO server-side encryption
mc admin config set minio encryption \
  kms_master_key=tenant-550e8400-key

-- In PostgreSQL session
SELECT tenants.current_tenant_id();
SELECT tenants.current_user_id();

SELECT
  u.email,
  tm.role,
  tm.joined_at
FROM tenants.tenant_members tm
JOIN auth.users u ON tm.user_id = u.id
WHERE tm.tenant_id = 'your-tenant-uuid';

-- View all RLS policies on a table
SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual
FROM pg_policies
WHERE tablename = 'users';

-- Test as Tenant A
SET hasura.user.x-hasura-tenant-id = 'tenant-a-uuid';
SELECT * FROM auth.users; -- Should only see Tenant A users

-- Test as Tenant B
SET hasura.user.x-hasura-tenant-id = 'tenant-b-uuid';
SELECT * FROM auth.users; -- Should only see Tenant B users

-- Connection pooling per tenant
ALTER SYSTEM SET max_connections = 500;
ALTER SYSTEM SET shared_buffers = '2GB';

-- Optimize for RLS queries
ALTER SYSTEM SET enable_partitionwise_join = on;
CREATE INDEX CONCURRENTLY idx_users_tenant_id ON auth.users(tenant_id);
CREATE INDEX CONCURRENTLY idx_sessions_tenant_id ON auth.sessions(tenant_id);

-- Statistics for query optimization
ALTER TABLE auth.users ALTER COLUMN tenant_id SET STATISTICS 1000;
ANALYZE auth.users;

-- Critical indexes for multi-tenant queries
CREATE INDEX CONCURRENTLY idx_users_tenant_email
  ON auth.users(tenant_id, email);

CREATE INDEX CONCURRENTLY idx_sessions_tenant_user
  ON auth.sessions(tenant_id, user_id);

CREATE INDEX CONCURRENTLY idx_tenant_members_lookup
  ON tenants.tenant_members(user_id, tenant_id);

-- Partial indexes for active tenants
CREATE INDEX CONCURRENTLY idx_active_tenants
  ON tenants.tenants(id) WHERE status = 'active';

-- Use tenant_id in all WHERE clauses
-- ❌ Slow (scans all rows)
SELECT * FROM users WHERE email = '[email protected]';

-- ✅ Fast (uses tenant + email index)
SELECT * FROM users
WHERE tenant_id = current_tenant_id()
AND email = '[email protected]';

-- Pre-aggregate tenant statistics
CREATE MATERIALIZED VIEW tenants.tenant_stats AS
SELECT
  t.id,
  t.slug,
  COUNT(DISTINCT u.id) as user_count,
  COUNT(DISTINCT s.id) as session_count,
  SUM(pg_total_relation_size(quote_ident('tenant_' || replace(t.id::text, '-', '_')))) as storage_bytes
FROM tenants.tenants t
LEFT JOIN auth.users u ON u.tenant_id = t.id
LEFT JOIN auth.sessions s ON s.tenant_id = t.id
WHERE t.status = 'active'
GROUP BY t.id;

-- Refresh hourly
CREATE INDEX ON tenants.tenant_stats(id);
REFRESH MATERIALIZED VIEW CONCURRENTLY tenants.tenant_stats;

-- Tenant health dashboard query
SELECT
  t.slug,
  t.status,
  t.plan_id,
  COUNT(DISTINCT u.id) as users,
  t.max_users,
  COUNT(DISTINCT s.id) as active_sessions,
  tenants.get_tenant_database_size(t.id) / 1073741824 as storage_gb,
  t.max_storage_gb,
  tenants.get_tenant_api_requests(t.id) as api_requests_this_month,
  t.max_api_requests_per_month
FROM tenants.tenants t
LEFT JOIN auth.users u ON u.tenant_id = t.id
LEFT JOIN auth.sessions s ON s.tenant_id = t.id
  AND s.created_at > NOW() - INTERVAL '1 hour'
WHERE t.status = 'active'
GROUP BY t.id;

-- All existing users go to default tenant
UPDATE auth.users
SET tenant_id = (SELECT id FROM tenants.tenants WHERE slug = 'default')
WHERE tenant_id IS NULL;

-- Create tenants from existing organizations
INSERT INTO tenants.tenants (slug, name, owner_user_id, plan_id)
SELECT
  lower(replace(org_name, ' ', '-')),
  org_name,
  org_owner_id,
  'pro'
FROM legacy_organizations;

-- Assign users to tenants based on organization
UPDATE auth.users u
SET tenant_id = t.id
FROM legacy_user_organizations luo
JOIN tenants.tenants t ON t.slug = lower(replace(luo.org_name, ' ', '-'))
WHERE u.id = luo.user_id;

-- Add tenant_id column to your tables
ALTER TABLE products ADD COLUMN tenant_id UUID;
ALTER TABLE orders ADD COLUMN tenant_id UUID;
ALTER TABLE invoices ADD COLUMN tenant_id UUID;

-- Backfill tenant_id based on user ownership
UPDATE products p
SET tenant_id = u.tenant_id
FROM auth.users u
WHERE p.created_by_user_id = u.id;

-- Make tenant_id NOT NULL after backfill
ALTER TABLE products ALTER COLUMN tenant_id SET NOT NULL;

-- Add foreign key constraint
ALTER TABLE products
  ADD CONSTRAINT fk_products_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants.tenants(id) ON DELETE CASCADE;

-- Add index
CREATE INDEX idx_products_tenant ON products(tenant_id);

-- Enable RLS
ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Create isolation policy
CREATE POLICY products_tenant_isolation ON products
  FOR ALL
  USING (tenant_id = tenants.current_tenant_id());

-- Grant access to Hasura
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO hasura;

-- Create pilot tenants
nself tenant create "Pilot Tenant 1" --slug pilot1
nself tenant create "Pilot Tenant 2" --slug pilot2

-- Migrate pilot users
UPDATE auth.users
SET tenant_id = (SELECT id FROM tenants.tenants WHERE slug = 'pilot1')
WHERE email IN ('[email protected]', '[email protected]');

-- Add nullable tenant_id
ALTER TABLE users ADD COLUMN tenant_id UUID;

-- Create default tenant
INSERT INTO tenants.tenants (slug, name, owner_user_id, plan_id)
VALUES ('default', 'Default Tenant', 'admin-user-uuid', 'enterprise');

-- Set default for new rows
ALTER TABLE users
  ALTER COLUMN tenant_id
  SET DEFAULT (SELECT id FROM tenants.tenants WHERE slug = 'default');

-- Backfill existing rows
UPDATE users
SET tenant_id = (SELECT id FROM tenants.tenants WHERE slug = 'default')
WHERE tenant_id IS NULL;

-- Make NOT NULL after backfill
ALTER TABLE users ALTER COLUMN tenant_id SET NOT NULL;

-- Enable RLS but create permissive policy initially
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Allow all access initially (no disruption)
CREATE POLICY users_allow_all ON users FOR ALL USING (true);

-- Later, switch to tenant isolation
DROP POLICY users_allow_all ON users;
CREATE POLICY users_tenant_isolation ON users
  FOR ALL
  USING (tenant_id = tenants.current_tenant_id());

-- Organizations can have multiple tenants
CREATE TABLE organizations.org_tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES organizations.organizations(id),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (org_id, tenant_id)
);

-- Users can belong to multiple organizations
CREATE TABLE organizations.org_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES organizations.organizations(id),
    user_id UUID NOT NULL,
    role TEXT NOT NULL DEFAULT 'member',
    UNIQUE (org_id, user_id)
);

-- Get all tenants for an organization
SELECT t.*
FROM tenants.tenants t
JOIN organizations.org_tenants ot ON t.id = ot.tenant_id
WHERE ot.org_id = 'org-uuid';

-- Get all users across organization's tenants
SELECT u.*
FROM auth.users u
JOIN tenants.tenants t ON u.tenant_id = t.id
JOIN organizations.org_tenants ot ON t.id = ot.tenant_id
WHERE ot.org_id = 'org-uuid';

-- Add composite indexes
CREATE INDEX idx_users_tenant_id ON users(tenant_id, id);

-- Analyze frequently
ANALYZE users;

-- Increase statistics target
ALTER TABLE users ALTER COLUMN tenant_id SET STATISTICS 1000;

-- Check if RLS is enabled
SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE tablename = 'users';

-- Check policies
SELECT * FROM pg_policies WHERE tablename = 'users';

-- Test policy
SET hasura.user.x-hasura-tenant-id = 'tenant-a-uuid';
SELECT * FROM users WHERE tenant_id = 'tenant-b-uuid';
-- Should return: 0 rows

-- Enable RLS if disabled
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Recreate policy if missing
DROP POLICY IF EXISTS users_tenant_isolation ON users;
CREATE POLICY users_tenant_isolation ON users
  FOR ALL
  USING (tenant_id = tenants.current_tenant_id());

EXPLAIN ANALYZE SELECT * FROM users WHERE tenant_id = current_tenant_id();

-- Add index on tenant_id
CREATE INDEX CONCURRENTLY idx_users_tenant ON users(tenant_id);

-- Use partial index for active tenants
CREATE INDEX CONCURRENTLY idx_active_tenant_users
  ON users(tenant_id, id)
  WHERE tenant_id IN (SELECT id FROM tenants.tenants WHERE status = 'active');

-- Increase work_mem for large result sets
SET work_mem = '256MB';

-- Check session variable
SHOW hasura.user.x-hasura-tenant-id;