-- Enable RLS on a table
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL,
  customer_name TEXT,
  total DECIMAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see rows matching their tenant_id
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Set tenant context per connection
SET app.current_tenant = '123e4567-e89b-12d3-a456-426614174000';

-- This query automatically filters to tenant_id = '123e...'
SELECT * FROM orders;

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'saas_app',
  max: 20
});

async function withTenantContext(tenantId, callback) {
  const client = await pool.connect();
  try {
    // CRITICAL: Set tenant context
    await client.query('SET LOCAL app.current_tenant = $1', [tenantId]);

    // Run user queries
    const result = await callback(client);

    return result;
  } finally {
    client.release(); // Returns to pool, context resets
  }
}

// Express middleware
app.use(async (req, res, next) => {
  const tenantId = extractTenantFromToken(req.headers.authorization);

  req.db = {
    query: (text, params) => withTenantContext(tenantId, client => 
      client.query(text, params)
    )
  };

  next();
});

// Route handler - automatic tenant filtering
app.get('/orders', async (req, res) => {
  const { rows } = await req.db.query('SELECT * FROM orders');
  res.json(rows); // Only tenant's orders returned
});

// DANGEROUS: Missing tenant context
const client = await pool.connect();
const { rows } = await client.query('SELECT * FROM orders'); // Returns ALL tenants!
client.release();

// SAFE: Always use wrapper
const { rows } = await req.db.query('SELECT * FROM orders');

-- Deny access by default, require explicit tenant context
CREATE POLICY deny_by_default ON orders
  USING (false);

-- Only allow access when tenant_id is set
CREATE POLICY allow_with_tenant ON orders
  USING (
    current_setting('app.current_tenant', true) IS NOT NULL 
    AND tenant_id = current_setting('app.current_tenant')::UUID
  );

pool.on('connect', client => {
  // Prevent queries without tenant context
  const originalQuery = client.query.bind(client);
  client.query = function(...args) {
    const tenantId = client._tenantId; // Set by withTenantContext
    if (!tenantId && process.env.NODE_ENV === 'production') {
      throw new Error('Query attempted without tenant context');
    }
    return originalQuery(...args);
  };
});

-- Log all queries with tenant context
CREATE TABLE query_audit (
  id BIGSERIAL PRIMARY KEY,
  tenant_id UUID,
  query_text TEXT,
  executed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger on every query (expensive, use sparingly)
CREATE OR REPLACE FUNCTION log_query() RETURNS event_trigger AS $$
BEGIN
  INSERT INTO query_audit (tenant_id, query_text)
  VALUES (
    current_setting('app.current_tenant', true)::UUID,
    current_query()
  );
END;
$$ LANGUAGE plpgsql;

-- Create schemas
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Identical table structure in each schema
CREATE TABLE tenant_acme.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_name TEXT,
  total DECIMAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tenant_globex.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_name TEXT,
  total DECIMAL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Set search path per connection
SET search_path TO tenant_acme;

-- Queries now automatically use tenant_acme.orders
SELECT * FROM orders;

const { Pool } = require('pg');

// Single pool, schema-per-tenant
const pool = new Pool({
  host: 'localhost',
  database: 'saas_app',
  max: 50 // Higher max for schema-per-tenant
});

async function withTenantSchema(tenantSlug, callback) {
  const client = await pool.connect();
  try {
    // Set schema search path
    await client.query(`SET search_path TO tenant_${tenantSlug}, public`);

    const result = await callback(client);
    return result;
  } finally {
    client.release();
  }
}

// Tenant provisioning
async function provisionTenant(tenantSlug) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Create schema
    await client.query(`CREATE SCHEMA IF NOT EXISTS tenant_${tenantSlug}`);

    // Run migrations for this schema
    await client.query(`SET search_path TO tenant_${tenantSlug}`);
    await client.query(`
      CREATE TABLE orders (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        customer_name TEXT,
        total DECIMAL,
        created_at TIMESTAMPTZ DEFAULT NOW()
      )
    `);

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Route handler
app.get('/orders', async (req, res) => {
  const tenantSlug = extractTenantSlug(req.headers.host); // e.g., acme.saas.com

  const { rows } = await withTenantSchema(tenantSlug, client =>
    client.query('SELECT * FROM orders')
  );

  res.json(rows);
});

const tenants = await getTenantList(); // ['acme', 'globex', ...]

for (const tenant of tenants) {
  await withTenantSchema(tenant, async client => {
    try {
      await client.query('BEGIN');

      // Add column
      await client.query(`
        ALTER TABLE orders 
        ADD COLUMN IF NOT EXISTS discount DECIMAL DEFAULT 0
      `);

      await client.query('COMMIT');

      console.log(`Migrated tenant: ${tenant}`);
    } catch (err) {
      await client.query('ROLLBACK');
      console.error(`Failed to migrate ${tenant}:`, err);
      // Continue to next tenant
    }
  });
}

const pLimit = require('p-limit');
const limit = pLimit(10); // 10 concurrent migrations

const migrations = tenants.map(tenant =>
  limit(() => withTenantSchema(tenant, migrateSchema))
);

await Promise.all(migrations);

-- Shared infrastructure (RLS)
CREATE TABLE public.tenants (
  id UUID PRIMARY KEY,
  slug TEXT UNIQUE,
  plan TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE public.users (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES public.tenants(id),
  email TEXT,
  role TEXT
);

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON public.users
  USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Tenant data (schema-per-tenant)
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.orders (...);

SET search_path TO tenant_acme, public;

-- Joins work across schemas
SELECT 
  o.id,
  o.total,
  u.email AS created_by
FROM orders o
JOIN users u ON o.created_by_user_id = u.id;

async function migrateToSchemas() {
  const tenants = await pool.query('SELECT id, slug FROM tenants');

  for (const tenant of tenants.rows) {
    console.log(`Migrating tenant: ${tenant.slug}`);

    // 1. Create schema
    await pool.query(`CREATE SCHEMA tenant_${tenant.slug}`);

    // 2. Copy table structure
    await pool.query(`
      CREATE TABLE tenant_${tenant.slug}.orders 
      (LIKE public.orders INCLUDING ALL)
    `);

    // 3. Copy data
    await pool.query(`
      INSERT INTO tenant_${tenant.slug}.orders
      SELECT * FROM public.orders WHERE tenant_id = $1
    `, [tenant.id]);

    // 4. Verify count
    const { rows: [oldCount] } = await pool.query(
      'SELECT COUNT(*) FROM public.orders WHERE tenant_id = $1',
      [tenant.id]
    );
    const { rows: [newCount] } = await pool.query(
      `SELECT COUNT(*) FROM tenant_${tenant.slug}.orders`
    );

    if (oldCount.count !== newCount.count) {
      throw new Error(`Migration failed for ${tenant.slug}`);
    }

    console.log(`Migrated ${oldCount.count} orders for ${tenant.slug}`);
  }

  // 5. Drop old table after cutover
  // await pool.query('DROP TABLE public.orders');
}

CREATE TABLE tenant_acme.orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  total DECIMAL
);

ALTER TABLE tenant_acme.orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_isolation ON tenant_acme.orders
  USING (user_id = current_setting('app.current_user')::UUID);

pg_dump -Fc saas_app > backup.dump

for schema in tenant_*; do
  pg_dump -Fc -n $schema saas_app > backup_$schema.dump
done

-- Restore specific schema to point in time
pg_restore -n tenant_acme -t orders backup.dump

# Export tenant schema
pg_dump -Fc -n tenant_acme saas_app > tenant_acme.dump

# Restore to new database
createdb tenant_acme_dedicated
pg_restore -d tenant_acme_dedicated -n public tenant_acme.dump

# Update routing
UPDATE tenants 
SET database_url = 'postgresql://host/tenant_acme_dedicated'
WHERE slug = 'acme';