import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import mysql from 'mysql2/promise';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const DATA_DIR = path.join(__dirname, 'data');
const SQL_FILE = path.join(DATA_DIR, 'tbl_kodepos.sql');
const SQL_URL =
	'https://raw.githubusercontent.com/edwin/database-kodepos-seluruh-indonesia/master/tbl_kodepos.sql';

const config = {
	host: process.env.MYSQL_HOST ?? 'localhost',
	port: Number(process.env.MYSQL_PORT ?? 3306),
	user: process.env.MYSQL_USER ?? 'root',
	password: process.env.MYSQL_PASSWORD ?? '',
	database: process.env.MYSQL_DATABASE ?? 'catatanwarung'
};

async function downloadSqlFile() {
	if (fs.existsSync(SQL_FILE)) {
		console.log('📦 File tbl_kodepos.sql sudah ada, lewati download.');
		return;
	}

	fs.mkdirSync(DATA_DIR, { recursive: true });
	console.log('⬇️  Mengunduh database kodepos dari GitHub...');

	const response = await fetch(SQL_URL);
	if (!response.ok) {
		throw new Error(`Gagal unduh: ${response.status} ${response.statusText}`);
	}

	const buffer = Buffer.from(await response.arrayBuffer());
	fs.writeFileSync(SQL_FILE, buffer);
	console.log(`✅ Tersimpan: ${SQL_FILE} (${(buffer.length / 1024 / 1024).toFixed(1)} MB)`);
}

function extractInsertStatements(sqlContent) {
	return sqlContent
		.split('\n')
		.map((line) => line.trim())
		.filter((line) => line.startsWith('INSERT INTO `tbl_kodepos`'));
}

async function importKodepos(connection) {
	const sqlContent = fs.readFileSync(SQL_FILE, 'utf8');
	const inserts = extractInsertStatements(sqlContent);

	if (inserts.length === 0) {
		throw new Error('Tidak ada data INSERT tbl_kodepos di file SQL.');
	}

	console.log(`📊 ${inserts.length.toLocaleString('id-ID')} baris kodepos siap diimpor.`);

	await connection.query('SET FOREIGN_KEY_CHECKS = 0');
	await connection.query('DROP TABLE IF EXISTS ref_kelurahan');
	await connection.query('DROP TABLE IF EXISTS ref_kecamatan');
	await connection.query('DROP TABLE IF EXISTS ref_kota');
	await connection.query('DROP TABLE IF EXISTS ref_provinsi');
	await connection.query('DROP TABLE IF EXISTS tbl_kodepos');

	await connection.query(`
		CREATE TABLE tbl_kodepos (
			id INT NOT NULL AUTO_INCREMENT,
			kelurahan VARCHAR(100) NOT NULL,
			kecamatan VARCHAR(100) NOT NULL,
			kabupaten VARCHAR(100) NOT NULL,
			provinsi VARCHAR(100) NOT NULL,
			kodepos VARCHAR(5) NOT NULL,
			PRIMARY KEY (id),
			INDEX ix_kodepos (kodepos),
			INDEX ix_provinsi (provinsi),
			INDEX ix_kabupaten (provinsi, kabupaten),
			INDEX ix_kecamatan (provinsi, kabupaten, kecamatan)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
	`);

	const batchSize = 400;
	for (let i = 0; i < inserts.length; i += batchSize) {
		const batch = inserts.slice(i, i + batchSize).join('\n');
		await connection.query(batch);

		if ((i + batchSize) % 4000 === 0 || i + batchSize >= inserts.length) {
			console.log(
				`   → ${Math.min(i + batchSize, inserts.length).toLocaleString('id-ID')} / ${inserts.length.toLocaleString('id-ID')}`
			);
		}
	}

	await connection.query('SET FOREIGN_KEY_CHECKS = 1');
}

async function main() {
	await downloadSqlFile();

	const connection = await mysql.createConnection({
		host: config.host,
		port: config.port,
		user: config.user,
		password: config.password,
		multipleStatements: true
	});

	try {
		await connection.query(`CREATE DATABASE IF NOT EXISTS \`${config.database}\``);
		await connection.query(`USE \`${config.database}\``);
		await importKodepos(connection);

		const [countRows] = await connection.query('SELECT COUNT(*) AS total FROM tbl_kodepos');
		const [provRows] = await connection.query('SELECT COUNT(DISTINCT provinsi) AS total FROM tbl_kodepos');

		console.log('');
		console.log('✅ Import kodepos selesai!');
		console.log(`   Database  : ${config.database}`);
		console.log(`   Tabel     : tbl_kodepos`);
		console.log(`   Total data: ${countRows[0].total.toLocaleString('id-ID')} kodepos`);
		console.log(`   Provinsi  : ${provRows[0].total} provinsi`);
		console.log('');
		console.log('   Sumber: https://github.com/edwin/database-kodepos-seluruh-indonesia');
	} finally {
		await connection.end();
	}
}

main().catch((error) => {
	console.error('❌ Gagal import kodepos:', error.message);
	process.exit(1);
});
