import assert from 'node:assert/strict';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const repoRoot = __dirname;
const schemaDir = path.join(repoRoot, 'schema');

const schemaSql = fs
    .readdirSync(schemaDir)
    .filter((name) => name.endsWith('.sql'))
    .sort()
    .map((name) => fs.readFileSync(path.join(schemaDir, name), 'utf8').toLowerCase())
    .join('\n');

function expectSql(pattern: RegExp, message: string) {
    assert(pattern.test(schemaSql), message);
}

// Core auth-scoped table protections currently defined in migrations.
expectSql(/\balter\s+table\s+trade_profiles\s+enable\s+row\s+level\s+security\b/, 'Missing RLS enable for trade_profiles');
expectSql(/\bcreate\s+policy\b[\s\S]*users can manage own profiles[\s\S]*on\s+trade_profiles\b/, 'Missing ownership policy on trade_profiles');

expectSql(/\balter\s+table\s+bot_config\s+enable\s+row\s+level\s+security\b/, 'Missing RLS enable for bot_config');
expectSql(/\bcreate\s+policy\b[\s\S]*authenticated users can read bot_config[\s\S]*on\s+bot_config\b/, 'Missing read policy on bot_config');
expectSql(/\bcreate\s+policy\b[\s\S]*admins can manage bot_config[\s\S]*on\s+bot_config\b/, 'Missing admin policy on bot_config');

expectSql(/\balter\s+table\s+orders\s+enable\s+row\s+level\s+security\b/, 'Missing RLS enable for orders');
expectSql(/\bcreate\s+policy\b[\s\S]*users can read own orders[\s\S]*on\s+orders\b/, 'Missing read policy on orders');
expectSql(/\bcreate\s+policy\b[\s\S]*users can insert own orders[\s\S]*on\s+orders\b/, 'Missing insert policy on orders');
expectSql(/\bcreate\s+policy\b[\s\S]*users can update own orders[\s\S]*on\s+orders\b/, 'Missing update policy on orders');

expectSql(/\balter\s+table\s+trade_history\s+enable\s+row\s+level\s+security\b/, 'Missing RLS enable for trade_history');
expectSql(/\bcreate\s+policy\b[\s\S]*users can read own trade history[\s\S]*on\s+trade_history\b/, 'Missing read policy on trade_history');
expectSql(/\bcreate\s+policy\b[\s\S]*users can insert own trade history[\s\S]*on\s+trade_history\b/, 'Missing insert policy on trade_history');
expectSql(/\bcreate\s+policy\b[\s\S]*users can update own trade history[\s\S]*on\s+trade_history\b/, 'Missing update policy on trade_history');

expectSql(/\bcreate\s+table\s+if\s+not\s+exists\s+bot_state_snapshots\b/, 'Missing bot_state_snapshots table definition');
expectSql(/\bcreate\s+policy\b[\s\S]*users can manage own snapshots[\s\S]*on\s+bot_state_snapshots\b/, 'Missing policy for bot_state_snapshots');

expectSql(/\bcreate\s+table\s+if\s+not\s+exists\s+capital_ledgers\b/, 'Missing capital_ledgers table definition');
expectSql(/\bcreate\s+policy\b[\s\S]*users can manage own ledger[\s\S]*on\s+capital_ledgers\b/, 'Missing policy for capital_ledgers');

console.log('[rls-policies] OK: required RLS enable statements and policies are present in schema migrations');