package main

import (
	"context"
	"fmt"
	"strings"
	"time"
)

// ============================================================================
// Location hierarchy
// Levels (in order): COUNTRY → STATE → DISTRICT → TALUK → CITY → ZONE → DMA → SITE
// ============================================================================

var locationLevelOrder = []string{
	"COUNTRY", "STATE", "DISTRICT", "TALUK", "CITY", "ZONE", "DMA", "SITE",
}

type LocationNode struct {
	LocationID       string          `json:"location_id"`
	LocationName     string          `json:"location_name"`
	LocationType     string          `json:"location_type"`
	ParentLocationID *string         `json:"parent_location_id"`
	DmaID            *string         `json:"dma_id"`
	ZoneID           *string         `json:"zone_id"`
	District         *string         `json:"district"`
	City             *string         `json:"city"`
	State            *string         `json:"state"`
	Country          *string         `json:"country"`
	Pincode          *string         `json:"pincode"`
	Latitude         *float64        `json:"latitude"`
	Longitude        *float64        `json:"longitude"`
	AreaSqkm         *float64        `json:"area_sqkm"`
	PopulationServed *int            `json:"population_served"`
	IsActive         bool            `json:"is_active"`
	CreatedAt        time.Time       `json:"created_at"`
	UpdatedAt        time.Time       `json:"updated_at"`
	Children         []*LocationNode `json:"children,omitempty"`
	AssetCount       int             `json:"asset_count"`
}

type LocationInput struct {
	LocationID       string   `json:"location_id"`
	LocationName     string   `json:"location_name"`
	LocationType     string   `json:"location_type"`
	ParentLocationID string   `json:"parent_location_id"`
	DmaID            string   `json:"dma_id"`
	ZoneID           string   `json:"zone_id"`
	District         string   `json:"district"`
	City             string   `json:"city"`
	State            string   `json:"state"`
	Country          string   `json:"country"`
	Pincode          string   `json:"pincode"`
	Latitude         *float64 `json:"latitude"`
	Longitude        *float64 `json:"longitude"`
	AreaSqkm         *float64 `json:"area_sqkm"`
	PopulationServed *int     `json:"population_served"`
	IsActive         *bool    `json:"is_active"`
}

func (db *DB) ListLocations(ctx context.Context) ([]*LocationNode, error) {
	rows, err := db.pool.Query(ctx, `
		SELECT l.location_id, l.location_name, l.location_type,
		       l.parent_location_id, l.dma_id, l.zone_id,
		       l.district, l.city, l.state, l.country, l.pincode,
		       l.latitude, l.longitude, l.area_sqkm, l.population_served,
		       l.is_active, l.created_at, l.updated_at,
		       COUNT(a.asset_id) AS asset_count
		FROM ic3_location_master l
		LEFT JOIN ic3_asset_master a ON a.location_id = l.location_id
		GROUP BY l.location_id
		ORDER BY l.location_type, l.location_name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var nodes []*LocationNode
	for rows.Next() {
		n := &LocationNode{}
		if err := rows.Scan(
			&n.LocationID, &n.LocationName, &n.LocationType,
			&n.ParentLocationID, &n.DmaID, &n.ZoneID,
			&n.District, &n.City, &n.State, &n.Country, &n.Pincode,
			&n.Latitude, &n.Longitude, &n.AreaSqkm, &n.PopulationServed,
			&n.IsActive, &n.CreatedAt, &n.UpdatedAt, &n.AssetCount,
		); err != nil {
			continue
		}
		nodes = append(nodes, n)
	}
	return nodes, nil
}

func (db *DB) GetLocationTree(ctx context.Context) ([]*LocationNode, error) {
	all, err := db.ListLocations(ctx)
	if err != nil {
		return nil, err
	}
	// Build map
	byID := make(map[string]*LocationNode, len(all))
	for _, n := range all {
		n.Children = []*LocationNode{}
		byID[n.LocationID] = n
	}
	// Attach children
	var roots []*LocationNode
	for _, n := range all {
		if n.ParentLocationID != nil && *n.ParentLocationID != "" {
			if parent, ok := byID[*n.ParentLocationID]; ok {
				parent.Children = append(parent.Children, n)
				continue
			}
		}
		roots = append(roots, n)
	}
	return roots, nil
}

func (db *DB) CreateLocation(ctx context.Context, in LocationInput) (*LocationNode, error) {
	if in.LocationID == "" || in.LocationName == "" || in.LocationType == "" {
		return nil, fmt.Errorf("location_id, location_name and location_type are required")
	}
	active := true
	if in.IsActive != nil {
		active = *in.IsActive
	}
	_, err := db.pool.Exec(ctx, `
		INSERT INTO ic3_location_master (
			location_id, location_name, location_type, parent_location_id,
			dma_id, zone_id, district, city, state, country, pincode,
			latitude, longitude, area_sqkm, population_served,
			is_active, created_at, updated_at
		) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,NOW(),NOW())`,
		in.LocationID, in.LocationName, strings.ToUpper(in.LocationType),
		nullStr(in.ParentLocationID), nullStr(in.DmaID), nullStr(in.ZoneID),
		nullStr(in.District), nullStr(in.City), nullStr(in.State),
		nullStr(in.Country), nullStr(in.Pincode),
		in.Latitude, in.Longitude, in.AreaSqkm, in.PopulationServed, active,
	)
	if err != nil {
		return nil, err
	}
	return db.getLocationByID(ctx, in.LocationID)
}

func (db *DB) UpdateLocation(ctx context.Context, id string, in LocationInput) (*LocationNode, error) {
	if in.LocationName == "" {
		return nil, fmt.Errorf("location_name is required")
	}
	active := true
	if in.IsActive != nil {
		active = *in.IsActive
	}
	_, err := db.pool.Exec(ctx, `
		UPDATE ic3_location_master SET
			location_name = $2, location_type = $3, parent_location_id = $4,
			dma_id = $5, zone_id = $6, district = $7, city = $8,
			state = $9, country = $10, pincode = $11,
			latitude = $12, longitude = $13, area_sqkm = $14,
			population_served = $15, is_active = $16, updated_at = NOW()
		WHERE location_id = $1`,
		id, in.LocationName, strings.ToUpper(in.LocationType),
		nullStr(in.ParentLocationID), nullStr(in.DmaID), nullStr(in.ZoneID),
		nullStr(in.District), nullStr(in.City), nullStr(in.State),
		nullStr(in.Country), nullStr(in.Pincode),
		in.Latitude, in.Longitude, in.AreaSqkm, in.PopulationServed, active,
	)
	if err != nil {
		return nil, err
	}
	return db.getLocationByID(ctx, id)
}

func (db *DB) DeleteLocation(ctx context.Context, id string) error {
	tag, err := db.pool.Exec(ctx, `DELETE FROM ic3_location_master WHERE location_id = $1`, id)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("not found")
	}
	return nil
}

func (db *DB) getLocationByID(ctx context.Context, id string) (*LocationNode, error) {
	n := &LocationNode{}
	err := db.pool.QueryRow(ctx, `
		SELECT location_id, location_name, location_type,
		       parent_location_id, dma_id, zone_id,
		       district, city, state, country, pincode,
		       latitude, longitude, area_sqkm, population_served,
		       is_active, created_at, updated_at
		FROM ic3_location_master WHERE location_id = $1`, id,
	).Scan(
		&n.LocationID, &n.LocationName, &n.LocationType,
		&n.ParentLocationID, &n.DmaID, &n.ZoneID,
		&n.District, &n.City, &n.State, &n.Country, &n.Pincode,
		&n.Latitude, &n.Longitude, &n.AreaSqkm, &n.PopulationServed,
		&n.IsActive, &n.CreatedAt, &n.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return n, nil
}

// ============================================================================
// Customer hierarchy
// Customer → Service Connection → Meter → Reading
// ============================================================================

type Customer struct {
	CustomerID      string     `json:"customer_id"`
	CustomerName    string     `json:"customer_name"`
	CustomerType    string     `json:"customer_type"`
	LocationID      *string    `json:"location_id"`
	DmaID           string     `json:"dma_id"`
	ZoneID          *string    `json:"zone_id"`
	Address         *string    `json:"address"`
	Pincode         *string    `json:"pincode"`
	Mobile          *string    `json:"mobile"`
	Email           *string    `json:"email"`
	AccountNo       *string    `json:"account_no"`
	ConnectionDate  *time.Time `json:"connection_date"`
	CustomerStatus  string     `json:"customer_status"`
	ConsentSMS      bool       `json:"consent_sms"`
	ConsentEmail    bool       `json:"consent_email"`
	CreatedAt       time.Time  `json:"created_at"`
	UpdatedAt       time.Time  `json:"updated_at"`
	// derived counts
	ConnectionCount int `json:"connection_count"`
}

type CustomerInput struct {
	CustomerID     string `json:"customer_id"`
	CustomerName   string `json:"customer_name"`
	CustomerType   string `json:"customer_type"`
	LocationID     string `json:"location_id"`
	DmaID          string `json:"dma_id"`
	ZoneID         string `json:"zone_id"`
	Address        string `json:"address"`
	Pincode        string `json:"pincode"`
	Mobile         string `json:"mobile"`
	Email          string `json:"email"`
	AccountNo      string `json:"account_no"`
	ConnectionDate string `json:"connection_date"`
	CustomerStatus string `json:"customer_status"`
	ConsentSMS     bool   `json:"consent_sms"`
	ConsentEmail   bool   `json:"consent_email"`
}

func (db *DB) ListCustomers(ctx context.Context, dmaID, customerType, status string, page, pageSize int) ([]Customer, int, error) {
	if pageSize <= 0 || pageSize > 500 {
		pageSize = 100
	}
	if page < 1 {
		page = 1
	}
	offset := (page - 1) * pageSize

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

	if dmaID != "" {
		where = append(where, fmt.Sprintf("c.dma_id = $%d", n))
		args = append(args, dmaID)
		n++
	}
	if customerType != "" {
		where = append(where, fmt.Sprintf("c.customer_type = $%d", n))
		args = append(args, customerType)
		n++
	}
	if status != "" {
		where = append(where, fmt.Sprintf("c.customer_status = $%d", n))
		args = append(args, status)
		n++
	}
	cond := strings.Join(where, " AND ")

	var total int
	db.pool.QueryRow(ctx, `SELECT COUNT(*) FROM crm_customer_master c WHERE `+cond, args...).Scan(&total)

	args = append(args, pageSize, offset)
	rows, err := db.pool.Query(ctx, fmt.Sprintf(`
		SELECT c.customer_id, c.customer_name, c.customer_type,
		       c.location_id, c.dma_id, c.zone_id,
		       c.address, c.pincode, c.mobile, c.email,
		       c.account_no, c.connection_date, c.customer_status,
		       c.consent_sms, c.consent_email,
		       c.created_at, c.updated_at,
		       COUNT(sc.connection_id) AS connection_count
		FROM crm_customer_master c
		LEFT JOIN crm_service_connection sc ON sc.customer_id = c.customer_id
		WHERE %s
		GROUP BY c.customer_id
		ORDER BY c.created_at DESC
		LIMIT $%d OFFSET $%d`, cond, n, n+1), args...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var out []Customer
	for rows.Next() {
		var cu Customer
		if err := rows.Scan(
			&cu.CustomerID, &cu.CustomerName, &cu.CustomerType,
			&cu.LocationID, &cu.DmaID, &cu.ZoneID,
			&cu.Address, &cu.Pincode, &cu.Mobile, &cu.Email,
			&cu.AccountNo, &cu.ConnectionDate, &cu.CustomerStatus,
			&cu.ConsentSMS, &cu.ConsentEmail,
			&cu.CreatedAt, &cu.UpdatedAt, &cu.ConnectionCount,
		); err != nil {
			continue
		}
		out = append(out, cu)
	}
	return out, total, nil
}

func (db *DB) CreateCustomer(ctx context.Context, in CustomerInput) (*Customer, error) {
	if in.CustomerID == "" || in.CustomerName == "" || in.DmaID == "" {
		return nil, fmt.Errorf("customer_id, customer_name and dma_id are required")
	}
	if in.CustomerStatus == "" {
		in.CustomerStatus = "Active"
	}
	if in.CustomerType == "" {
		in.CustomerType = "Domestic"
	}
	_, err := db.pool.Exec(ctx, `
		INSERT INTO crm_customer_master (
			customer_id, customer_name, customer_type, location_id,
			dma_id, zone_id, address, pincode, mobile, email,
			account_no, connection_date, customer_status,
			consent_sms, consent_email, created_at, updated_at
		) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,NOW(),NOW())`,
		in.CustomerID, in.CustomerName, in.CustomerType, nullStr(in.LocationID),
		in.DmaID, nullStr(in.ZoneID), nullStr(in.Address), nullStr(in.Pincode),
		nullStr(in.Mobile), nullStr(in.Email), nullStr(in.AccountNo),
		nullStr(in.ConnectionDate), in.CustomerStatus,
		in.ConsentSMS, in.ConsentEmail,
	)
	if err != nil {
		return nil, err
	}
	return db.getCustomerByID(ctx, in.CustomerID)
}

func (db *DB) UpdateCustomer(ctx context.Context, id string, in CustomerInput) (*Customer, error) {
	if in.CustomerName == "" {
		return nil, fmt.Errorf("customer_name is required")
	}
	_, err := db.pool.Exec(ctx, `
		UPDATE crm_customer_master SET
			customer_name = $2, customer_type = $3, location_id = $4,
			dma_id = $5, zone_id = $6, address = $7, pincode = $8,
			mobile = $9, email = $10, account_no = $11,
			connection_date = $12, customer_status = $13,
			consent_sms = $14, consent_email = $15, updated_at = NOW()
		WHERE customer_id = $1`,
		id, in.CustomerName, in.CustomerType, nullStr(in.LocationID),
		in.DmaID, nullStr(in.ZoneID), nullStr(in.Address), nullStr(in.Pincode),
		nullStr(in.Mobile), nullStr(in.Email), nullStr(in.AccountNo),
		nullStr(in.ConnectionDate), in.CustomerStatus,
		in.ConsentSMS, in.ConsentEmail,
	)
	if err != nil {
		return nil, err
	}
	return db.getCustomerByID(ctx, id)
}

func (db *DB) DeleteCustomer(ctx context.Context, id string) error {
	tag, err := db.pool.Exec(ctx, `DELETE FROM crm_customer_master WHERE customer_id = $1`, id)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("not found")
	}
	return nil
}

func (db *DB) getCustomerByID(ctx context.Context, id string) (*Customer, error) {
	cu := &Customer{}
	err := db.pool.QueryRow(ctx, `
		SELECT customer_id, customer_name, customer_type,
		       location_id, dma_id, zone_id, address, pincode, mobile, email,
		       account_no, connection_date, customer_status,
		       consent_sms, consent_email, created_at, updated_at
		FROM crm_customer_master WHERE customer_id = $1`, id,
	).Scan(
		&cu.CustomerID, &cu.CustomerName, &cu.CustomerType,
		&cu.LocationID, &cu.DmaID, &cu.ZoneID, &cu.Address, &cu.Pincode,
		&cu.Mobile, &cu.Email, &cu.AccountNo, &cu.ConnectionDate,
		&cu.CustomerStatus, &cu.ConsentSMS, &cu.ConsentEmail,
		&cu.CreatedAt, &cu.UpdatedAt,
	)
	return cu, err
}

// ── Service Connections ───────────────────────────────────────────────────────

type ServiceConnection struct {
	ConnectionID     string     `json:"connection_id"`
	CustomerID       string     `json:"customer_id"`
	MeterID          *string    `json:"meter_id"`
	AssetID          *string    `json:"asset_id"`
	DmaID            string     `json:"dma_id"`
	ConnectionType   string     `json:"connection_type"`
	PipeSizeMm       *int       `json:"pipe_size_mm"`
	ConnectionStatus string     `json:"connection_status"`
	ConnectionDate   *time.Time `json:"connection_date"`
	LastMeterReadM3  *float64   `json:"last_meter_read_m3"`
	AvgDailyDemandM3 *float64   `json:"avg_daily_demand_m3"`
	CreatedAt        time.Time  `json:"created_at"`
}

func (db *DB) ListServiceConnections(ctx context.Context, customerID string) ([]ServiceConnection, error) {
	rows, err := db.pool.Query(ctx, `
		SELECT connection_id, customer_id, meter_id, asset_id,
		       dma_id, connection_type, pipe_size_mm,
		       connection_status, connection_date,
		       last_meter_read_m3, avg_daily_demand_m3, created_at
		FROM crm_service_connection WHERE customer_id = $1
		ORDER BY created_at DESC`, customerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []ServiceConnection
	for rows.Next() {
		var sc ServiceConnection
		if err := rows.Scan(
			&sc.ConnectionID, &sc.CustomerID, &sc.MeterID, &sc.AssetID,
			&sc.DmaID, &sc.ConnectionType, &sc.PipeSizeMm,
			&sc.ConnectionStatus, &sc.ConnectionDate,
			&sc.LastMeterReadM3, &sc.AvgDailyDemandM3, &sc.CreatedAt,
		); err != nil {
			continue
		}
		out = append(out, sc)
	}
	if out == nil {
		out = []ServiceConnection{}
	}
	return out, nil
}

func (db *DB) CreateServiceConnection(ctx context.Context, conn ServiceConnection) (*ServiceConnection, error) {
	if conn.ConnectionID == "" || conn.CustomerID == "" || conn.DmaID == "" {
		return nil, fmt.Errorf("connection_id, customer_id and dma_id required")
	}
	if conn.ConnectionStatus == "" {
		conn.ConnectionStatus = "Active"
	}
	if conn.ConnectionType == "" {
		conn.ConnectionType = "Domestic"
	}
	_, err := db.pool.Exec(ctx, `
		INSERT INTO crm_service_connection (
			connection_id, customer_id, meter_id, asset_id,
			dma_id, connection_type, pipe_size_mm,
			connection_status, connection_date,
			last_meter_read_m3, avg_daily_demand_m3, created_at, updated_at
		) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,NOW(),NOW())`,
		conn.ConnectionID, conn.CustomerID, conn.MeterID, conn.AssetID,
		conn.DmaID, conn.ConnectionType, conn.PipeSizeMm,
		conn.ConnectionStatus, conn.ConnectionDate,
		conn.LastMeterReadM3, conn.AvgDailyDemandM3,
	)
	if err != nil {
		return nil, err
	}
	return &conn, nil
}

// ── DMA summary for location dropdown ────────────────────────────────────────

func (db *DB) GetDMASummary(ctx context.Context) ([]map[string]any, error) {
	rows, err := db.pool.Query(ctx, `
		SELECT dma_id, COUNT(*) AS asset_count,
		       SUM(CASE WHEN asset_status='Active' THEN 1 ELSE 0 END) AS active_count
		FROM ic3_asset_master
		WHERE dma_id IS NOT NULL
		GROUP BY dma_id ORDER BY dma_id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []map[string]any
	for rows.Next() {
		var dmaID string
		var total, active int
		rows.Scan(&dmaID, &total, &active)
		out = append(out, map[string]any{"dma_id": dmaID, "asset_count": total, "active_count": active})
	}
	return out, nil
}