const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
	host: process.env.DB_HOST || 'localhost',
	port: process.env.DB_PORT || 5432,
	database: process.env.DB_NAME || 'vietprodev_sso',
	user: process.env.DB_USER || 'postgres',
	password: process.env.DB_PASSWORD,
});

async function checkDatabase() {
	const client = await pool.connect();
	try {
		console.log('🔍 Checking database tables...\n');

		// Get all tables
		const tablesResult = await client.query(`
			SELECT table_name 
			FROM information_schema.tables 
			WHERE table_schema = 'public' 
			ORDER BY table_name;
		`);

		const tables = tablesResult.rows.map((row) => row.table_name);
		console.log('📋 Existing tables:');
		tables.forEach((table) => console.log(`  ✓ ${table}`));

		// Expected tables (SSO core schema)
		const expectedTables = [
			// Auth
			'users',
			'user_auth',
			'user_sessions',
			'user_password_history',
			'password_reset_tokens',
			'email_verify_tokens',
			// RBAC
			'roles',
			'permissions',
			'user_roles',
			'role_permissions',
			// Files
			'files',
			'file_variants',
			// Notifications
			'notification_messages',
			'notification_receipts',
			'notification_devices',
			'notification_delivery_attempts',
			'notification_preferences',
			// OIDC
			'oidc_grants',
			'clients',
			// Webhooks
			'webhook_logs',
		];

		console.log('\n📊 Table status:');
		expectedTables.forEach((table) => {
			const exists = tables.includes(table);
			console.log(`  ${exists ? '✅' : '❌'} ${table}`);
		});

		const missing = expectedTables.filter((t) => !tables.includes(t));
		if (missing.length > 0) {
			console.log('\n⚠️  Missing tables:', missing.join(', '));
		} else {
			console.log('\n✅ All expected tables exist!');
		}

		// Check enums
		const enumsResult = await client.query(`
			SELECT typname 
			FROM pg_type 
			WHERE typtype = 'e' 
			ORDER BY typname;
		`);

		const enums = enumsResult.rows.map((row) => row.typname);
		console.log('\n📋 Existing enums:');
		enums.forEach((enumName) => console.log(`  ✓ ${enumName}`));
	} catch (error) {
		console.error('❌ Error checking database:', error.message);
	} finally {
		client.release();
		await pool.end();
	}
}

checkDatabase();
