import express from "express";
import { insert, select, update, execute } from "../db/queryHelper/index.js";

const router = express.Router();

// 1. CREATE Location
router.post("/", async (req, res) => {
	try {
		const {
			user_id,
			lat,
			lng,
			source_type,
			source_confidence,
			selected_location,
			is_saved_address,
			location_type,
			country,
			state,
			district,
			city_village,
			pincode,
		} = req.body;

		// Validate user exists if this is a saved address
		let finalUserId = user_id;
		if (is_saved_address && user_id) {
			const userCheck = await select({
				table: 'users',
				columns: ['id'],
				where: { id: user_id, deleted: false },
				limit: 1
			});
			if (userCheck.length === 0) {
				return res.status(400).json({ 
					error: `User with id ${user_id} does not exist. Cannot create saved address for non-existent user.` 
				});
			}
		} else if (!is_saved_address) {
			// For captured locations (not saved addresses), user_id can be NULL
			finalUserId = null;
		} else if (is_saved_address && !user_id) {
			return res.status(400).json({ 
				error: "user_id is required when is_saved_address is true" 
			});
		}

		// Insert into locations
		const locationData = {
			user_id: finalUserId,
			lat,
			lng,
			source_type: source_type || "manual",
			source_confidence: source_confidence || "low",
			selected_location: selected_location || false,
			is_saved_address: is_saved_address || false,
			location_type: location_type || "other",
			country,
			state,
			district,
			city_village,
			pincode,
		};

		const locationResult = await insert({
			table: 'locations',
			data: locationData,
			returning: '*'
		});

		res.status(201).json(locationResult);
	} catch (err) {
		console.error("Error creating location:", err);
		
		// Provide more specific error messages
		if (err.code === '23503') { // Foreign key violation
			return res.status(400).json({ 
				error: `Foreign key constraint violation: ${err.detail || 'The provided user_id does not exist in the users table.'}` 
			});
		}
		
		res.status(500).json({ error: err.message || "Internal server error" });
	}
});

// 2. GET User Locations
router.get("/user/:userId", async (req, res) => {
	try {
		const { userId } = req.params;
		const result = await select({
			table: 'locations',
			where: { user_id: userId, deleted: false },
			orderBy: { column: 'created_at', direction: 'desc' }
		});
		res.json(result);
	} catch (err) {
		console.error("Error fetching user locations:", err);
		res.status(500).json({ error: "Internal server error" });
	}
});

// 3. GET Single Location
router.get("/:id", async (req, res) => {
	try {
		const { id } = req.params;
		const result = await select({
			table: 'locations',
			where: { id, deleted: false },
			limit: 1
		});

		if (result.length === 0) {
			return res.status(404).json({ error: "Location not found" });
		}

		res.json(result[0]);
	} catch (err) {
		console.error("Error fetching location:", err);
		res.status(500).json({ error: "Internal server error" });
	}
});

// 4. UPDATE Location
router.put("/:id", async (req, res) => {
	try {
		const { id } = req.params;
		const {
			lat,
			lng,
			source_type,
            source_confidence,
            selected_location,
			is_saved_address,
			location_type,
			country,
			state,
			district,
			city_village,
			pincode,
		} = req.body;

		// Use raw builder for COALESCE functionality
		const result = await execute({
			type: 'raw-builder',
			handler: async (knex) => {
				const updates = {};
				if (lat !== undefined) updates.lat = knex.raw('COALESCE(?, lat)', [lat]);
				if (lng !== undefined) updates.lng = knex.raw('COALESCE(?, lng)', [lng]);
				if (source_type !== undefined) updates.source_type = knex.raw('COALESCE(?, source_type)', [source_type]);
				if (source_confidence !== undefined) updates.source_confidence = knex.raw('COALESCE(?, source_confidence)', [source_confidence]);
				if (selected_location !== undefined) updates.selected_location = knex.raw('COALESCE(?, selected_location)', [selected_location]);
				if (is_saved_address !== undefined) updates.is_saved_address = knex.raw('COALESCE(?, is_saved_address)', [is_saved_address]);
				if (location_type !== undefined) updates.location_type = knex.raw('COALESCE(?, location_type)', [location_type]);
				if (country !== undefined) updates.country = knex.raw('COALESCE(?, country)', [country]);
				if (state !== undefined) updates.state = knex.raw('COALESCE(?, state)', [state]);
				if (district !== undefined) updates.district = knex.raw('COALESCE(?, district)', [district]);
				if (city_village !== undefined) updates.city_village = knex.raw('COALESCE(?, city_village)', [city_village]);
				if (pincode !== undefined) updates.pincode = knex.raw('COALESCE(?, pincode)', [pincode]);
				
				return await knex('locations')
					.where({ id, deleted: false })
					.update(updates)
					.returning('*');
			}
		});

		if (result.length === 0) {
			return res.status(404).json({ error: "Location not found" });
		}

		res.json(result[0]);
	} catch (err) {
		console.error("Error updating location:", err);
		res.status(500).json({ error: "Internal server error" });
	}
});

// 5. DELETE Location
router.delete("/:id", async (req, res) => {
	try {
		const { id } = req.params;
		const result = await update({
			table: 'locations',
			data: { deleted: true },
			where: { id },
			returning: ['id']
		});

		if (result.length === 0) {
			return res.status(404).json({ error: "Location not found" });
		}

		res.json({ message: "Location deleted successfully" });
	} catch (err) {
		console.error("Error deleting location:", err);
		res.status(500).json({ error: "Internal server error" });
	}
});

export default router;