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 || 'bekind',
	user: process.env.DB_USER || 'postgres',
	password: process.env.DB_PASSWORD,
});

async function runMigration() {
	const client = await pool.connect();
	try {
		console.log('🚀 Starting database migration...');

		// Check if schema_migrations table exists
		const { rows: tableExists } = await client.query(`
			SELECT EXISTS (
				SELECT FROM information_schema.tables 
				WHERE table_schema = 'public' 
				AND table_name = 'schema_migrations'
			);
		`);

		const isNewTrackingTable = !tableExists[0].exists;

		// Create migrations tracking table if it doesn't exist
		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
			);
		`);

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

		// Get already applied migrations
		let appliedSet = new Set();
		const { rows: appliedMigrations } = await client.query('SELECT migration_name FROM schema_migrations');
		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.size === 0) {
			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'];
				for (const name of baseline) {
					if (migrationFiles.includes(name)) {
						await client.query('INSERT INTO schema_migrations (migration_name) VALUES ($1)', [name]);
						appliedSet.add(name);
						console.log(`✅ Marked as applied: ${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);
			const sql = fs.readFileSync(filePath, 'utf8');

			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();
		await pool.end();
	}
}

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);
}
