const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
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,
});

// Run the consolidated clean migration first, then any incremental migrations
async function runMigration() {
	const client = await pool.connect();
	try {
		console.log('🚀 Starting SSO database migration...');

		// Track which migrations have been applied
		await client.query(`
			CREATE TABLE IF NOT EXISTS schema_migrations (
				id SERIAL PRIMARY KEY,
				migration_name VARCHAR(255) UNIQUE NOT NULL,
				applied_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
			);
		`);

		// Always run clean-migration.sql first (idempotent consolidated schema)
		const cleanMigrationPath = path.join(__dirname, '../clean-migration.sql');
		if (fs.existsSync(cleanMigrationPath)) {
			const { rows: applied } = await client.query(
				"SELECT 1 FROM schema_migrations WHERE migration_name = 'clean-migration.sql'",
			);
			if (applied.length === 0) {
				console.log('📄 Running clean-migration.sql (consolidated schema)...');
				const sql = fs.readFileSync(cleanMigrationPath, 'utf8');
				await client.query('BEGIN');
				try {
					await client.query(sql);
					await client.query(
						'INSERT INTO schema_migrations (migration_name) VALUES ($1)',
						['clean-migration.sql'],
					);
					await client.query('COMMIT');
					console.log('✅ Completed: clean-migration.sql');
				} catch (error) {
					await client.query('ROLLBACK');
					throw error;
				}
			} else {
				console.log('⏭️  Skipping already applied: clean-migration.sql');
			}
		}

		const migrationsDir = path.join(__dirname, '../migrations');
		const migrationFiles = fs
			.readdirSync(migrationsDir)
			.filter((f) => f.endsWith('.sql'))
			.sort();

		// Get already applied migrations
		const { rows: appliedMigrations } = await client.query(
			'SELECT migration_name FROM schema_migrations',
		);
		const appliedSet = new Set(appliedMigrations.map((m) => m.migration_name));

		// If tracking table is empty and user_auth table exists, mark baseline as applied
		if (!appliedSet.has('clean-migration.sql')) {
			const { rows: userAuthExists } = await client.query(`
				SELECT EXISTS (
					SELECT FROM information_schema.tables
					WHERE table_schema = 'public'
					AND table_name = 'user_auth'
				);
			`);

			if (userAuthExists[0].exists) {
				console.log('📋 Detected existing database, marking baseline migrations as applied...');
				const baseline = [
					'001-auth-schema.sql',
					'002-file-schema.sql',
					'003-notification-schema.sql',
					'004-consolidated-migrations.sql',
				];
				for (const name of baseline) {
					if (migrationFiles.includes(name)) {
						await client.query(
							'INSERT INTO schema_migrations (migration_name) VALUES ($1) ON CONFLICT DO NOTHING',
							[name],
						);
						appliedSet.add(name);
						console.log(`✅ Marked as applied: ${name}`);
					}
				}
			}
		} else {
			// clean-migration.sql is a consolidated superset of 001-004.
			// Mark those legacy incremental files as applied so they don't re-run and conflict.
			const legacyBaseline = [
				'001-auth-schema.sql',
				'002-file-schema.sql',
				'003-notification-schema.sql',
				'004-consolidated-migrations.sql',
			];
			for (const name of legacyBaseline) {
				if (migrationFiles.includes(name) && !appliedSet.has(name)) {
					await client.query(
						'INSERT INTO schema_migrations (migration_name) VALUES ($1) ON CONFLICT DO NOTHING',
						[name],
					);
					appliedSet.add(name);
					console.log(`✅ Marked as applied (covered by clean-migration.sql): ${name}`);
				}
			}
		}

		for (const file of migrationFiles) {
			if (appliedSet.has(file)) {
				console.log(`⏭️  Skipping already applied: ${file}`);
				continue;
			}

			console.log(`📄 Running migration: ${file}`);
			const filePath = path.join(migrationsDir, file);
			let sql = fs.readFileSync(filePath, 'utf8');

			// Make incremental migrations idempotent: add IF NOT EXISTS to CREATE INDEX
			// (Postgres supports this since 9.5, but legacy migration files don't use it).
			// CREATE TABLE IF NOT EXISTS already covers tables, but CREATE INDEX without
			// IF NOT EXISTS conflicts when clean-migration.sql already created the index.
			// Line-by-line replace to avoid multi-line regex issues with CRLF or leading whitespace.
			const sqlLines = sql.split(/\r?\n/);
			for (let i = 0; i < sqlLines.length; i++) {
				if (/^CREATE INDEX IF NOT EXISTS /.test(sqlLines[i])) {
					// Already idempotent, no change
				} else if (/^CREATE INDEX /.test(sqlLines[i])) {
					sqlLines[i] = 'CREATE INDEX IF NOT EXISTS' + sqlLines[i].slice('CREATE INDEX'.length);
				} else if (/^CREATE UNIQUE INDEX IF NOT EXISTS /.test(sqlLines[i])) {
					// Already idempotent
				} else if (/^CREATE UNIQUE INDEX /.test(sqlLines[i])) {
					sqlLines[i] =
						'CREATE UNIQUE INDEX IF NOT EXISTS' + sqlLines[i].slice('CREATE UNIQUE INDEX'.length);
				} else if (/^CREATE TABLE audit\..*PARTITION OF audit\..*DEFAULT/.test(sqlLines[i])) {
					// Default partition: CREATE TABLE x PARTITION OF y DEFAULT → add IF NOT EXISTS
					sqlLines[i] = sqlLines[i].replace(/^CREATE TABLE /, 'CREATE TABLE IF NOT EXISTS ');
				}
			}
			// Triggers have no IF NOT EXISTS in Postgres. Drop and recreate to make idempotent.
			// A CREATE TRIGGER statement may span multiple lines; collect (name, table) pairs
			// and emit DROP TRIGGER IF EXISTS for each at the top of the script.
			const triggerDrops = [];
			for (let i = 0; i < sqlLines.length; i++) {
				const tmatch = sqlLines[i].match(/^CREATE TRIGGER (\w+)\b/);
				if (tmatch) {
					// Look ahead up to 4 lines for the ON <table> clause
					for (let j = i; j < Math.min(i + 5, sqlLines.length); j++) {
						const tm = sqlLines[j].match(/\bON\s+([\w."]+)/);
						if (tm) {
							triggerDrops.push(`DROP TRIGGER IF EXISTS ${tmatch[1]} ON ${tm[1]};`);
							break;
						}
					}
				}
			}
			if (triggerDrops.length > 0) {
				sql = triggerDrops.join('\n') + '\n' + sqlLines.join('\n');
			} else {
				sql = sqlLines.join('\n');
			}

			await client.query('BEGIN');
			try {
				await client.query(sql);
				await client.query('INSERT INTO schema_migrations (migration_name) VALUES ($1)', [file]);
				await client.query('COMMIT');
				console.log(`✅ Completed: ${file}`);
			} catch (error) {
				await client.query('ROLLBACK');
				throw error;
			}
		}

		console.log('🎉 All migrations completed successfully!');
	} catch (error) {
		console.error('❌ Migration failed:', error);
		throw error;
	} finally {
		client.release();
		// Note: pool.end() is intentionally NOT called here. When `db:setup`
		// runs command "all", runSeed() uses the same pool and will close it.
	}
}

async function runSeed() {
	const client = await pool.connect();
	try {
		console.log('\n🌱 Starting database seeding...');

		const seedsDir = path.join(__dirname, '../seeds');
		if (!fs.existsSync(seedsDir)) {
			console.log('⚠️  No seeds directory found, skipping seed.');
			return;
		}

		const seedFiles = fs
			.readdirSync(seedsDir)
			.filter((f) => f.endsWith('.sql'))
			.sort();

		if (seedFiles.length === 0) {
			console.log('⚠️  No seed files found, skipping seed.');
			return;
		}

		for (const file of seedFiles) {
			console.log(`📄 Running seed: ${file}`);
			const filePath = path.join(seedsDir, file);
			const sql = fs.readFileSync(filePath, 'utf8');

			await client.query(sql);
			console.log(`✅ Completed: ${file}`);
		}

		console.log('🎉 All seeds completed successfully!');
	} catch (error) {
		console.error('❌ Seeding failed:', error);
		throw error;
	} finally {
		client.release();
		await pool.end();
	}
}

const command = process.argv[2] || 'migrate';

if (command === 'migrate') {
	runMigration().catch((err) => {
		console.error(err);
		process.exit(1);
	});
} else if (command === 'seed') {
	runSeed().catch((err) => {
		console.error(err);
		process.exit(1);
	});
} else if (command === 'all') {
	runMigration()
		.then(() => runSeed())
		.catch((err) => {
			console.error(err);
			process.exit(1);
		});
} else {
	console.log('Usage: node migrate.js [migrate|seed|all]');
	console.log('  migrate  - Run database migrations only');
	console.log('  seed     - Run database seeds only');
	console.log('  all      - Run migrations then seeds (default)');
	process.exit(1);
}
