scripts/verify_rls.js

import postgres from 'postgres';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const envPath = path.join(__dirname, '../artifacts/api-server/.env');
let connectionString = process.env.DATABASE_URL;

if (!connectionString && fs.existsSync(envPath)) {
  const envContent = fs.readFileSync(envPath, 'utf8');
  const match = envContent.match(/DATABASE_URL=(.*)/);
  if (match) connectionString = match[1].trim();
}

if (!connectionString) {
  console.error("DATABASE_URL is not set");
  process.exit(1);
}


const sql = postgres(connectionString);

const USER_A = '00000000-0000-0000-0000-000000000001';
const USER_B = '00000000-0000-0000-0000-000000000002';

async function runTest() {
  try {
    console.log("--- Starting Comprehensive RLS Verification Test ---");

    const tables = ['clients', 'projects', 'tasks', 'time_entries', 'briefs'];
    
    // 1. Clean up
    for (const table of tables) {
      await sql.unsafe(`DELETE FROM ${table} WHERE user_id IN ('${USER_A}', '${USER_B}')`);
    }
    console.log("✓ Cleaned up old test data for all tables.");

    const runAsUser = async (userId, queryFn) => {
      return await sql.begin(async (tx) => {
        await tx.unsafe(`SET LOCAL role TO authenticated`);
        await tx.unsafe(`SET LOCAL "request.jwt.claims" TO '{"sub": "${userId}"}'`);
        return await queryFn(tx);
      });
    };

    // Test each table
    for (const table of tables) {
      console.log(`\n--- Testing Table: ${table} ---`);

      // Prepare dummy data for insertion
      let data = { user_id: USER_A };
      if (table === 'clients') { data.name = 'Test Client A'; data.email = 'a@test.com'; }
      if (table === 'projects') { data.title = 'Test Project A'; }
      if (table === 'tasks') { 
        // Need a project first
        const p = await runAsUser(USER_A, tx => tx`INSERT INTO projects (title, user_id) VALUES ('Project for Task', ${USER_A}) RETURNING id`);
        data.title = 'Test Task A'; data.project_id = p[0].id;
      }
      if (table === 'time_entries') {
         const p = await runAsUser(USER_A, tx => tx`INSERT INTO projects (title, user_id) VALUES ('Project for Time', ${USER_A}) RETURNING id`);
         data.hours = 1.5; data.date = '2024-01-01'; data.project_id = p[0].id;
      }
      if (table === 'briefs') { data.title = 'Test Brief A'; data.content = 'Content A'; }

      // 2. Insert
      console.log(`Testing: User A inserts into ${table}...`);
      await runAsUser(USER_A, async (tx) => {
        await tx.unsafe(`INSERT INTO ${table} (${Object.keys(data).join(',')}) VALUES (${Object.values(data).map(v => typeof v === 'string' ? `'${v}'` : v).join(',')})`);
      });
      console.log(`✓ User A successfully inserted into ${table}.`);

      // 3. Select (Self)
      const rowsA = await runAsUser(USER_A, async (tx) => {
        return await tx.unsafe(`SELECT * FROM ${table} WHERE user_id = '${USER_A}'`);
      });
      if (rowsA.length > 0) {
        console.log(`✓ User A successfully read their own data from ${table}.`);
      } else {
        throw new Error(`User A could not read their own data from ${table}!`);
      }

      // 4. Select (Other)
      const rowsB = await runAsUser(USER_B, async (tx) => {
        return await tx.unsafe(`SELECT * FROM ${table} WHERE user_id = '${USER_A}'`);
      });
      if (rowsB.length === 0) {
        console.log(`✓ User B cannot see User A's data in ${table} (RLS Working).`);
      } else {
        throw new Error(`CRITICAL: User B can see User A's data in ${table}!`);
      }

      // 5. Update (Other)
      const updateCol = (table === 'clients' ? 'name' : (table === 'time_entries' ? 'description' : 'title'));
      const updateResult = await runAsUser(USER_B, async (tx) => {
        return await tx.unsafe(`UPDATE ${table} SET ${updateCol} = 'Hacked' WHERE user_id = '${USER_A}'`);
      });
      if (updateResult.count === 0) {
        console.log(`✓ User B cannot update User A's data in ${table} (RLS Working).`);
      } else {
        throw new Error(`CRITICAL: User B updated User A's data in ${table}!`);
      }


      // 6. Delete (Other)
      const deleteResult = await runAsUser(USER_B, async (tx) => {
        return await tx.unsafe(`DELETE FROM ${table} WHERE user_id = '${USER_A}'`);
      });
      if (deleteResult.count === 0) {
        console.log(`✓ User B cannot delete User A's data in ${table} (RLS Working).`);
      } else {
        throw new Error(`CRITICAL: User B deleted User A's data in ${table}!`);
      }
    }

    console.log("\n--- All Comprehensive RLS Verification Tests Passed Successfully! ---");

  } catch (err) {
    console.error("\nFAIL: RLS Verification Failed!");
    console.error(err.stack || err.message);
    process.exit(1);
  } finally {
    await sql.end();
  }
}


runTest();