package main

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"strconv"
	"strings"
	"time"
)

// GET /api/scope/customers — lists ic3_customer_master for scope dropdown
func listScopeCustomersHandler(db *DB) http.HandlerFunc {
	type item struct {
		CustomerID   int    `json:"customer_id"`
		CustomerName string `json:"customer_name"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		rows, err := db.pool.Query(ctx,
			`SELECT customer_id, customer_name FROM ic3_customer_master ORDER BY customer_name`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.CustomerID, &x.CustomerName) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/locations?customer_id=X — lists ic3_location_master filtered by customer.
// Returns lat/lng so the GIS Auto-Map panel can show the location centre immediately.
func listScopeLocationsHandler(db *DB) http.HandlerFunc {
	type item struct {
		LocationID   int      `json:"location_id"`
		LocationName string   `json:"location_name"`
		Latitude     *float64 `json:"latitude"`
		Longitude    *float64 `json:"longitude"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		query := `SELECT location_id, location_name, latitude, longitude FROM ic3_location_master`
		args := []any{}
		if cid := r.URL.Query().Get("customer_id"); cid != "" {
			id, err := strconv.Atoi(cid)
			if err != nil {
				w.WriteHeader(400)
				json.NewEncoder(w).Encode(map[string]string{"error": "invalid customer_id"})
				return
			}
			query += ` WHERE customer_id = $1`
			args = append(args, id)
		}
		query += ` ORDER BY location_name`

		rows, err := db.pool.Query(ctx, query, args...)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.LocationID, &x.LocationName, &x.Latitude, &x.Longitude) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/cities — all cities from ic3_city for city scope dropdown
func listScopeCitiesHandler(db *DB) http.HandlerFunc {
	type item struct {
		CityID   string `json:"city_id"`
		CityName string `json:"city_name"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		rows, err := db.pool.Query(ctx,
			`SELECT city_id, city_name FROM ic3_city ORDER BY city_name`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.CityID, &x.CityName) == nil {
				out = append(out, x)
			}
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/customer-locations?city_id=DEL — customer+location pairs, optionally filtered by city
func listScopeCustomerLocationsHandler(db *DB) http.HandlerFunc {
	type item struct {
		CustomerID   int    `json:"customer_id"`
		CustomerName string `json:"customer_name"`
		LocationID   int    `json:"location_id"`
		LocationName string `json:"location_name"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		q := `
			SELECT cm.customer_id, cm.customer_name, lm.location_id, lm.location_name
			FROM ic3_customer_master cm
			INNER JOIN ic3_location_master lm ON lm.customer_id = cm.customer_id`
		args := []any{}
		if cityID := r.URL.Query().Get("city_id"); cityID != "" {
			q += ` WHERE lm.city_id = $1`
			args = append(args, cityID)
		}
		q += ` ORDER BY cm.customer_name, lm.location_name`

		rows, err := db.pool.Query(ctx, q, args...)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.CustomerID, &x.CustomerName, &x.LocationID, &x.LocationName) == nil {
				out = append(out, x)
			}
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/ic3-systems — all active systems from ic3_system_master grouped by category.
func listAllIC3SystemsHandler(db *DB) http.HandlerFunc {
	type item struct {
		SystemID       string `json:"system_id"`
		SystemName     string `json:"system_name"`
		SystemCategory string `json:"system_category"`
		SystemLabel    string `json:"system_label"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()
		rows, err := db.pool.Query(ctx, `
			SELECT system_id, system_name, system_category, COALESCE(system_label, '')
			FROM ic3_system_master
			WHERE is_active = true AND (system_type = 'DOMAIN' OR system_type IS NULL)
			ORDER BY system_id
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.SystemID, &x.SystemName, &x.SystemCategory, &x.SystemLabel) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/filter-asset-categories?customer_id=&location_id=&system_id=
// Returns distinct asset categories that have assets matching the given scope filters.
func listFilteredAssetCategoriesHandler(db *DB) http.HandlerFunc {
	type item struct {
		CatID        int    `json:"cat_id"`
		CategoryName string `json:"category_name"`
		Count        int    `json:"count"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		where := []string{}
		args := []any{}
		n := 1

		if v := r.URL.Query().Get("customer_id"); v != "" {
			id, err := strconv.Atoi(v)
			if err == nil {
				where = append(where, fmt.Sprintf("am.customer_id = $%d", n))
				args = append(args, id)
				n++
			}
		}
		if v := r.URL.Query().Get("location_id"); v != "" {
			id, err := strconv.Atoi(v)
			if err == nil {
				where = append(where, fmt.Sprintf("am.location_id = $%d", n))
				args = append(args, id)
				n++
			}
		}
		if v := r.URL.Query().Get("system_id"); v != "" {
			where = append(where, fmt.Sprintf("am.source_system_id = $%d", n))
			args = append(args, v)
			n++
		}

		whereClause := ""
		if len(where) > 0 {
			whereClause = "WHERE " + strings.Join(where, " AND ")
		}

		sql := `
			SELECT ac.cat_id, ac.category_name, COUNT(am.asset_id) AS cnt
			FROM ic3_asset_category ac
			INNER JOIN ic3_asset_group ag ON ag.cat_id = ac.cat_id
			INNER JOIN ic3_asset_master am ON am.asset_group_id = ag.subcategory_id
			` + whereClause + `
			GROUP BY ac.cat_id, ac.category_name
			ORDER BY cnt DESC, ac.category_name`

		rows, err := db.pool.Query(ctx, sql, args...)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.CatID, &x.CategoryName, &x.Count) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/filter-asset-groups?customer_id=&location_id=&system_id=&cat_id=
// Returns asset groups for the given scope and category filters.
func listFilteredAssetGroupsHandler(db *DB) http.HandlerFunc {
	type item struct {
		SubcategoryID  int    `json:"subcategory_id"`
		AssetGroupName string `json:"asset_group_name"`
		Count          int    `json:"count"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		ctx, cancel := context.WithTimeout(r.Context(), 8*time.Second)
		defer cancel()

		where := []string{}
		args := []any{}
		n := 1

		if v := r.URL.Query().Get("cat_id"); v != "" {
			id, err := strconv.Atoi(v)
			if err == nil {
				where = append(where, fmt.Sprintf("ag.cat_id = $%d", n))
				args = append(args, id)
				n++
			}
		}
		if v := r.URL.Query().Get("customer_id"); v != "" {
			id, err := strconv.Atoi(v)
			if err == nil {
				where = append(where, fmt.Sprintf("am.customer_id = $%d", n))
				args = append(args, id)
				n++
			}
		}
		if v := r.URL.Query().Get("location_id"); v != "" {
			id, err := strconv.Atoi(v)
			if err == nil {
				where = append(where, fmt.Sprintf("am.location_id = $%d", n))
				args = append(args, id)
				n++
			}
		}
		if v := r.URL.Query().Get("system_id"); v != "" {
			where = append(where, fmt.Sprintf("am.source_system_id = $%d", n))
			args = append(args, v)
			n++
		}

		whereClause := ""
		if len(where) > 0 {
			whereClause = "WHERE " + strings.Join(where, " AND ")
		}

		sql := `
			SELECT ag.subcategory_id, ag.asset_group_name, COUNT(am.asset_id) AS cnt
			FROM ic3_asset_group ag
			INNER JOIN ic3_asset_master am ON am.asset_group_id = ag.subcategory_id
			` + whereClause + `
			GROUP BY ag.subcategory_id, ag.asset_group_name
			ORDER BY cnt DESC, ag.asset_group_name`

		rows, err := db.pool.Query(ctx, sql, args...)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.SubcategoryID, &x.AssetGroupName, &x.Count) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/customers-by-city?city_id=DEL — customers with locations in the given city
func listCustomersByCityHandler(db *DB) http.HandlerFunc {
	type item struct {
		CustomerID   int    `json:"customer_id"`
		CustomerName string `json:"customer_name"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		cityID := r.URL.Query().Get("city_id")
		if cityID == "" {
			w.WriteHeader(400)
			json.NewEncoder(w).Encode(map[string]string{"error": "city_id required"})
			return
		}

		rows, err := db.pool.Query(ctx, `
			SELECT customer_id, customer_name
			FROM ic3_customer_master
			WHERE city_id = $1
			ORDER BY customer_name
		`, cityID)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			if rows.Scan(&x.CustomerID, &x.CustomerName) == nil {
				out = append(out, x)
			}
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(out)
	}
}

// GET /api/scope/solution-scenarios — lists all active Solution Scenario entries from ic3_system_master.
// Returns the 12 scenarios plus the 2 platform sub-layers (CMS/CMMS).
func listSolutionScenariosHandler(db *DB) http.HandlerFunc {
	type scenario struct {
		SystemID   string `json:"system_id"`
		SystemName string `json:"system_name"`
		SystemType string `json:"system_type"`
	}
	type response struct {
		Scenarios []scenario `json:"scenarios"`
		Platforms []scenario `json:"platforms"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT system_id, system_name, system_type
			FROM ic3_system_master
			WHERE is_active = true
			  AND system_category IN ('SOLUTION_SCENARIO', 'PLATFORM')
			ORDER BY system_category DESC, system_id
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()

		var res response
		for rows.Next() {
			var s scenario
			if rows.Scan(&s.SystemID, &s.SystemName, &s.SystemType) == nil {
				if s.SystemType == "PLATFORM" {
					res.Platforms = append(res.Platforms, s)
				} else {
					res.Scenarios = append(res.Scenarios, s)
				}
			}
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(res)
	}
}

// GET /api/scope/systems — lists active DOMAIN systems from ic3_system_master.
func listScopeSystemsHandler(db *DB) http.HandlerFunc {
	type item struct {
		SystemID   string  `json:"system_id"`
		SystemName string  `json:"system_name"`
		ParentID   *string `json:"parent_id"`
		ParentName *string `json:"parent_name"`
	}
	return func(w http.ResponseWriter, r *http.Request) {
		if db == nil {
			w.WriteHeader(503)
			json.NewEncoder(w).Encode(map[string]string{"error": "database unavailable"})
			return
		}
		ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second)
		defer cancel()

		rows, err := db.pool.Query(ctx, `
			SELECT system_id, system_name
			FROM   ic3_system_master
			WHERE  is_active = true AND system_type = 'DOMAIN'
			ORDER  BY system_name
		`)
		if err != nil {
			w.WriteHeader(500)
			json.NewEncoder(w).Encode(map[string]string{"error": err.Error()})
			return
		}
		defer rows.Close()
		out := []item{}
		for rows.Next() {
			var x item
			x.ParentID = nil
			x.ParentName = nil
			if rows.Scan(&x.SystemID, &x.SystemName) == nil {
				out = append(out, x)
			}
		}
		json.NewEncoder(w).Encode(out)
	}
}