const {
	pool
} = require('../db');
const path = require('path');
const fs = require('fs');

class DeclarationService {
	async createDeclarationRecord(conn, uuid, type, data, paymentId = null) {
		const [result] = await conn.execute(
			`INSERT INTO declarations (uuid, declaration_type, user_name, user_firstname, user_address, user_zip, user_city, user_country, user_ide, language, payment_id, status) VALUES (?, ?, ?, ?, ?, ?, ?, 'CH', ?, ?, ?, 'draft')`,
			[
				uuid,
				type.toUpperCase(),
				data.user_name || '',
				data.user_firstname || null,
				data.user_address || null,
				data.user_zip || '',
				data.user_city || '',
				data.user_ide || null,
				(data.language || 'fr').toLowerCase(),
				paymentId
			]
		);
		return result.insertId;
	}

	async createImportDetailsRecord(conn, declarationId, data) {
		return conn.execute(
			`INSERT INTO import_details (declaration_id, dispatch_country, transport_mode, is_relocation, purchase_price, purchase_currency, transport_cost, other_costs, statistical_value, vat_value) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			[
				declarationId,
				data.dispatch_country,
				data.transport_mode,
				data.is_relocation ? 1 : 0,
				Number(data.purchase_price || 0),
				data.purchase_currency,
				Number(data.transport_cost || 0),
				Number(data.other_costs || 0),
				data.statistical_value_chf,
				data.vat_value_chf
			]
		);
	}

	async createExportDetailsRecord(conn, declarationId, data) {
		return conn.execute(
			`INSERT INTO export_details (declaration_id, destination_country, taxation_code) VALUES (?, ?, ?)`,
			[
				declarationId,
				data.destination_country,
				data.taxation_code || null
			]
		);
	}

	async createVehicleRecord(conn, declarationId, data) {
		return conn.execute(
			`INSERT INTO vehicles (declaration_id, vin, brand, model, year, cylinder_capacity, fuel_type, weight_empty, weight_total, tares_code, statistical_key, country_origin, brand_code, matriculation_number) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			[
				declarationId,
				(data.vin || '').toUpperCase(),
				data.brand,
				data.model,
				data.year || null,
				data.cylinder_capacity || null,
				data.fuel_type || null,
				data.weight_empty || null,
				data.weight_total || null,
				data.commodity_code || (data.declaration_type === 'EXPORT' ? '8703.9060' : null),
				data.statistical_key || '911',
				data.origin_country || null,
				data.brand_code || null,
				data.matriculation_number || null
			]
		);
	}

	async createFullImportDeclaration(conn, txId, data, paymentId) {
		const declarationId = await this.createDeclarationRecord(conn, txId, 'IMPORT', data, paymentId);
		await this.createImportDetailsRecord(conn, declarationId, data);
		await this.createVehicleRecord(conn, declarationId, {
			...data,
			declaration_type: 'IMPORT'
		});
		return declarationId;
	}

	async createFullExportDeclaration(conn, txId, data, paymentId) {
		const declarationId = await this.createDeclarationRecord(conn, txId, 'EXPORT', data, paymentId);
		await this.createExportDetailsRecord(conn, declarationId, data);
		await this.createVehicleRecord(conn, declarationId, {
			...data,
			declaration_type: 'EXPORT'
		});
		return declarationId;
	}

	async saveXmlFile(conn, txId, xml, declarationId) {
		const xmlDir = path.join(__dirname, '../../xml');
		if (!fs.existsSync(xmlDir)) {
			fs.mkdirSync(xmlDir, {
				recursive: true
			});
		}
		const xmlFilePath = path.join(xmlDir, `${txId}.xml`);
		fs.writeFileSync(xmlFilePath, xml, 'utf8');

		await conn.execute(
			`UPDATE declarations SET xml_content = ?, xml_file_path = ?, status = 'generated' WHERE id = ?`,
			[xml, xmlFilePath, declarationId]
		);
		return xmlFilePath;
	}

	getSubmissionProgress(status) {
		let progress = 0;
		let step = '';
		switch (status) {
			case 'pending_payment':
				progress = 20;
				step = 'En attente de paiement';
				break;
			case 'payment_completed':
				progress = 40;
				step = 'Paiement validé, préparation de la soumission';
				break;
			case 'generated':
				progress = 25;
				step = 'Déclaration générée, en attente de soumission';
				break;
			case 'submitting':
				progress = 50;
				step = 'Connexion au portail e-dec';
				break;
			case 'processing':
				progress = 75;
				step = 'Traitement par la douane';
				break;
			case 'submitted':
				progress = 100;
				step = 'Soumission terminée avec succès';
				break;
			case 'submission_error':
				progress = 100;
				step = 'Erreur de soumission';
				break;
			default:
				progress = 10;
				step = 'Initialisation';
		}
		return {
			progress,
			step
		};
	}

	async getBrandCodeByBrandName(brandName) {
		if (!brandName) return null;
		const cleanedBrandName = brandName.trim().toUpperCase();
		const query = `SELECT brand_code FROM brand_codes WHERE brand_name = ?`;
		const [rows] = await pool.execute(query, [cleanedBrandName]);
		return rows.length > 0 ? rows[0].brand_code : null;
	}

	async getAllBrandCodes() {
		const query = `SELECT brand_code, brand_name FROM brand_codes ORDER BY brand_name`;
		const [rows] = await pool.execute(query);
		return rows;
	}
}

module.exports = new DeclarationService();
