package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
)

// GetNavMenu returns the full menu tree for a system filtered by role.
// role_code="" means no filtering (SUPER_ADMIN).
func (db *DB) GetNavMenu(ctx context.Context, systemID, roleCode string) (*NavMenuResponse, error) {
	// 1. Get system name
	var sysName string
	err := db.pool.QueryRow(ctx,
		`SELECT system_name FROM ic3_system_master WHERE system_id=$1`, systemID,
	).Scan(&sysName)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	// Debug: Check data availability
	var layerCount, groupCount, itemCount int
	db.pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_layer").Scan(&layerCount)
	db.pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_group WHERE system_id = $1", systemID).Scan(&groupCount)
	db.pool.QueryRow(ctx, "SELECT COUNT(*) FROM ic3_nav_item i JOIN ic3_nav_group g ON g.id = i.group_id WHERE g.system_id = $1", systemID).Scan(&itemCount)
	log.Printf("[NAV] System: %s, Layers: %d, Groups: %d, Items: %d", systemID, layerCount, groupCount, itemCount)

	// 2. Single query: layer → group → item (left join role_access)
	// Use ic3_system_nav bridge table to get layers for this system
	rows, err := db.pool.Query(ctx, `
		SELECT
			l.layer_no, l.code AS layer_code, l.label AS layer_label,
			g.id AS group_id, g.code AS group_code, g.label AS group_label,
				COALESCE(g.icon,'') AS group_icon, g.sort_order AS group_sort,
			i.id AS item_id, i.code AS item_code, i.label AS item_label,
				COALESCE(i.route,'') AS item_route,
				COALESCE(i.icon,'')  AS item_icon,
				i.sort_order AS item_sort, i.status
		FROM ic3_nav_layer l
		JOIN ic3_system_nav sn ON sn.layer_id = l.id AND sn.system_id = $1 AND sn.status = 'active'
		JOIN ic3_nav_group g ON g.layer_id = l.id AND g.system_id = $1 AND g.is_active = TRUE
		JOIN ic3_nav_item  i ON i.group_id = g.id AND i.is_active = TRUE
		LEFT JOIN ic3_nav_role_access ra ON ra.item_id = i.id AND ra.role_code = $2
		WHERE (ra.id IS NULL OR ra.can_view = TRUE)
		ORDER BY l.layer_no, g.sort_order, i.sort_order
	`, systemID, roleCode)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 3. Build tree in memory
	resp := &NavMenuResponse{SystemID: systemID, SystemName: sysName}
	layerIndex := map[int]int{}              // layerNo -> index in resp.Layers
	groupIndex := map[string]int{}           // "layerNo_groupID" -> index in resp.Layers[x].Groups
	rowCount := 0
	groupItemCounts := map[int]int{}

	for rows.Next() {
		rowCount++
		var (
			layerNo, groupSort, itemSort int
			layerCode, layerLabel        string
			groupID                      int
			groupCode, groupLabel, groupIcon string
			itemID                       int
			itemCode, itemLabel, itemRoute, itemIcon, status string
		)
		if err := rows.Scan(
			&layerNo, &layerCode, &layerLabel,
			&groupID, &groupCode, &groupLabel, &groupIcon, &groupSort,
			&itemID, &itemCode, &itemLabel, &itemRoute, &itemIcon, &itemSort, &status,
		); err != nil {
			return nil, err
		}

		// layer - create if not exists
		layerIdx, ok := layerIndex[layerNo]
		if !ok {
			resp.Layers = append(resp.Layers, NavLayerTree{
				LayerNo: layerNo, Code: layerCode, Label: layerLabel,
			})
			layerIdx = len(resp.Layers) - 1
			layerIndex[layerNo] = layerIdx
		}

		// group - create if not exists (use compound key to handle same group ID in different layers)
		groupKey := fmt.Sprintf("%d_%d", layerNo, groupID)
		groupIdx, ok := groupIndex[groupKey]
		if !ok {
			resp.Layers[layerIdx].Groups = append(resp.Layers[layerIdx].Groups, NavGroupTree{
				ID: groupID, Code: groupCode, Label: groupLabel, Icon: groupIcon,
			})
			groupIdx = len(resp.Layers[layerIdx].Groups) - 1
			groupIndex[groupKey] = groupIdx
			log.Printf("[NAV-DEBUG] Created group %d (key: %s) at index %d in layer %d", groupID, groupKey, groupIdx, layerNo)
		}

		// item - always append
		resp.Layers[layerIdx].Groups[groupIdx].Items = append(
			resp.Layers[layerIdx].Groups[groupIdx].Items,
			NavItem{
				ID: itemID, GroupID: groupID,
				Code: itemCode, Label: itemLabel,
				Route: itemRoute, Icon: itemIcon,
				SortOrder: itemSort, Status: status,
			},
		)
		groupItemCounts[groupID]++
	}

	log.Printf("[NAV-DEBUG] Query returned %d rows. Groups with items: %v", rowCount, groupItemCounts)
	return resp, rows.Err()
}

// GetSystemLayers returns all layers for a specific system
func (db *DB) GetSystemLayers(ctx context.Context, systemID string) ([]map[string]interface{}, error) {
	// Query layers linked to this system via ic3_system_nav bridge table
	rows, err := db.pool.Query(ctx, `
		SELECT DISTINCT l.id, l.layer_no, l.code, l.label, l.description
		FROM ic3_nav_layer l
		JOIN ic3_system_nav sn ON sn.layer_id = l.id
		WHERE sn.system_id = $1 AND sn.status = 'active'
		ORDER BY l.layer_no ASC
	`, systemID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var layers []map[string]interface{}
	for rows.Next() {
		var (
			id          int
			layerNo     int
			code        string
			label       string
			description sql.NullString
		)
		if err := rows.Scan(&id, &layerNo, &code, &label, &description); err != nil {
			return nil, err
		}

		layer := map[string]interface{}{
			"id":          id,
			"layer_id":    id,
			"layer_no":    layerNo,
			"code":        code,
			"name":        label,
			"label":       label,
			"description": description.String,
		}
		layers = append(layers, layer)
	}

	return layers, rows.Err()
}