-- ============================================================================
-- PostgreSQL Row-Level Security (RLS) Setup
-- ============================================================================
-- This script sets up database-level RLS as the last line of defense
-- Use with ORM middleware for defense-in-depth security

-- ============================================================================
-- 1. Enable RLS on Tables
-- ============================================================================

-- Enable RLS on all multi-tenant tables
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- 2. Create Policies for Tenant Isolation
-- ============================================================================

-- Customers table - tenant isolation
CREATE POLICY tenant_isolation_customers ON customers
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- Orders table - tenant isolation
CREATE POLICY tenant_isolation_orders ON orders
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- Products table - tenant isolation
CREATE POLICY tenant_isolation_products ON products
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- Invoices table - tenant isolation
CREATE POLICY tenant_isolation_invoices ON invoices
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- Payments table - tenant isolation
CREATE POLICY tenant_isolation_payments ON payments
    FOR ALL
    USING (tenant_id = current_setting('app.tenant_id', true)::int)
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::int);

-- ============================================================================
-- 3. Create Policies for User Ownership
-- ============================================================================

-- Orders table - users can only see their own orders
CREATE POLICY user_ownership_orders ON orders
    FOR ALL
    USING (
        user_id = current_setting('app.user_id', true)::int 
        OR current_setting('app.role', true) = 'admin'
    )
    WITH CHECK (
        user_id = current_setting('app.user_id', true)::int
        OR current_setting('app.role', true) = 'admin'
    );

-- Payments table - users can only see their own payments
CREATE POLICY user_ownership_payments ON payments
    FOR ALL
    USING (
        user_id = current_setting('app.user_id', true)::int
        OR current_setting('app.role', true) IN ('admin', 'finance')
    )
    WITH CHECK (
        user_id = current_setting('app.user_id', true)::int
        OR current_setting('app.role', true) IN ('admin', 'finance')
    );

-- ============================================================================
-- 4. Test RLS Setup
-- ============================================================================

-- Set context for tenant 1
SET LOCAL app.tenant_id = '1';
SET LOCAL app.user_id = '123';
SET LOCAL app.role = 'user';

-- This query will only return records where tenant_id = 1
SELECT * FROM customers;

-- Reset context
RESET app.tenant_id;
RESET app.user_id;
RESET app.role;

-- ============================================================================
-- 5. Integration with ORM
-- ============================================================================

-- In your application code, set context before each query:
-- 
-- PostgreSQL adapter example:
-- await pool.query('SET LOCAL app.tenant_id = $1', [user.tenantId]);
-- await pool.query('SET LOCAL app.user_id = $1', [user.id]);
-- await pool.query('SET LOCAL app.role = $1', [user.role]);
-- 
-- Then run your ORM queries - RLS is enforced automatically

-- ============================================================================
-- 6. Drop Policies (for cleanup/testing)
-- ============================================================================

-- Uncomment to drop policies:
-- DROP POLICY IF EXISTS tenant_isolation_customers ON customers;
-- DROP POLICY IF EXISTS tenant_isolation_orders ON orders;
-- DROP POLICY IF EXISTS tenant_isolation_products ON products;
-- DROP POLICY IF EXISTS user_ownership_orders ON orders;
-- DROP POLICY IF EXISTS user_ownership_payments ON payments;

-- Uncomment to disable RLS:
-- ALTER TABLE customers DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE orders DISABLE ROW LEVEL SECURITY;
-- ALTER TABLE products DISABLE ROW LEVEL SECURITY;
