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 schemaFiles = fs
    .readdirSync(schemaDir)
    .filter((name) => name.endsWith('.sql'))
    .sort();

const combinedSql = schemaFiles
    .map((name) => fs.readFileSync(path.join(schemaDir, name), 'utf8').toLowerCase())
    .join('\n');

function assertSql(pattern: RegExp, message: string) {
    assert(pattern.test(combinedSql), message);
}

// Core tables used by runtime.
assertSql(/\bcreate\s+table\s+if\s+not\s+exists\s+trade_profiles\b/, 'Missing trade_profiles table definition');
assertSql(/\bcreate\s+table\s+if\s+not\s+exists\s+bot_config\b/, 'Missing bot_config table definition');
assertSql(/\bcreate\s+table\s+if\s+not\s+exists\s+dynamic_config\b/, 'Missing dynamic_config table definition');

// Orders columns controlled by local migrations.
assertSql(/\balter\s+table\s+orders\b/, 'orders migration statements missing');
assertSql(/\borders\b[\s\S]*\bprofile_id\b/, 'orders.profile_id not found in migration contract');
assertSql(/\borders\b[\s\S]*\bstop_loss\b/, 'orders.stop_loss not found in migration contract');
assertSql(/\borders\b[\s\S]*\btake_profit\b/, 'orders.take_profit not found in migration contract');
assertSql(/\borders\b[\s\S]*\baction\b/, 'orders.action not found in migration contract');
assertSql(/\borders\b[\s\S]*\btrade_id\b/, 'orders.trade_id not found in migration contract');
assertSql(/\borders\b[\s\S]*\bsub_tag\b/, 'orders.sub_tag not found in migration contract');

// Capital ledger reserve formula must include realized_pnl to match runtime available-capital math.
assertSql(
    /\bcreate\s+or\s+replace\s+function\s+fn_reserve_for_order\b[\s\S]*allocated_capital\s*\+\s*realized_pnl[\s\S]*reserved_for_orders[\s\S]*reserved_for_positions[\s\S]*>=\s*p_amount/,
    'fn_reserve_for_order must gate reservation using allocated_capital + realized_pnl - reserved balances'
);

// Trade history columns used by dashboard/bot traceability.
assertSql(/\btrade_history\b[\s\S]*\bprofile_id\b/, 'trade_history.profile_id not found in migration contract');
assertSql(/\btrade_history\b[\s\S]*\brules_metadata\b/, 'trade_history.rules_metadata not found in migration contract');
assertSql(/\btrade_history\b[\s\S]*\btrade_id\b/, 'trade_history.trade_id not found in migration contract');
assertSql(/\btrade_history\b[\s\S]*\bsource\b/, 'trade_history.source not found in migration contract');

// RLS presence for critical user-scoped tables.
assertSql(/\balter\s+table\s+trade_profiles\s+enable\s+row\s+level\s+security\b/, 'trade_profiles RLS enable statement missing');
assertSql(/\bcreate\s+policy\b[\s\S]*trade_profiles\b/, 'trade_profiles policy definition missing');

console.log(`[schema-contract] OK: validated ${schemaFiles.length} schema files`);