package repository

import (
	"users_management/m/model/dto/req"
	"users_management/m/model/dto/res"
	"users_management/m/model/entity"

	"github.com/google/uuid"
	"gorm.io/gorm"
)

type CableConnectionRepo interface {
	// Basic CRUD operations
	Create(connection entity.CableConnection) error
	GetByID(id uuid.UUID) (entity.CableConnection, error)
	GetByIDWithDevices(id uuid.UUID) (entity.CableConnection, error)
	Update(id uuid.UUID, request req.UpdateCableConnectionDTO) error
	Delete(id uuid.UUID) error

	// Bulk operations
	BulkCreate(connections []entity.CableConnection) ([]entity.CableConnection, []error)
	BulkUpdate(ids []uuid.UUID, updates req.UpdateCableConnectionDTO) (int64, error)
	BulkDelete(ids []uuid.UUID) (int64, error)

	// Search and filtering
	SearchWithPagination(request req.CableConnectionSearchDTO) ([]entity.CableConnection, int, error)
	GetByDeviceID(deviceID uuid.UUID) ([]entity.CableConnection, error)

	// Analytics operations
	GetLengthDistribution(cableType string) (res.CableLengthDistributionResponse, error)
	GetTypeAnalytics() (res.CableTypeAnalyticsResponse, error)
	GetStatusSummary() (res.CableStatusSummaryResponse, error)

	// Route analysis
	FindPossibleRoutes(fromDeviceID, toDeviceID uuid.UUID) ([]res.RouteConnectionResponse, error)
	TracePath(fromDeviceID, toDeviceID uuid.UUID, maxHops int) (res.CablePathResponse, error)
	GetNetworkMap(deviceType, cableType, region string) (res.NetworkMapResponse, error)

	// Maintenance operations
	UpdateStatus(id uuid.UUID, status string, notes *string) error
	GetMaintenanceDue(days int) ([]res.MaintenanceItemResponse, error)
}

type cableConnectionRepo struct {
	db *gorm.DB
}

func NewCableConnectionRepo(db *gorm.DB) CableConnectionRepo {
	return &cableConnectionRepo{db: db}
}

func (r *cableConnectionRepo) Create(connection entity.CableConnection) error {
	return r.db.Create(&connection).Error
}

func (r *cableConnectionRepo) GetByID(id uuid.UUID) (entity.CableConnection, error) {
	var connection entity.CableConnection
	err := r.db.First(&connection, "id = ?", id).Error
	return connection, err
}

func (r *cableConnectionRepo) GetByIDWithDevices(id uuid.UUID) (entity.CableConnection, error) {
	var connection entity.CableConnection
	err := r.db.Preload("FromDevice").Preload("ToDevice").First(&connection, "id = ?", id).Error
	return connection, err
}

func (r *cableConnectionRepo) Update(id uuid.UUID, request req.UpdateCableConnectionDTO) error {
	updates := make(map[string]interface{})

	if request.FromDeviceID != nil {
		updates["from_device_id"] = *request.FromDeviceID
	}
	if request.ToDeviceID != nil {
		updates["to_device_id"] = *request.ToDeviceID
	}
	if request.CableLength != nil {
		updates["cable_length"] = *request.CableLength
	}
	if request.CableType != nil {
		updates["cable_type"] = *request.CableType
	}
	if request.BranchingType != nil {
		updates["branching_type"] = *request.BranchingType
	}
	if request.InstallationDate != nil {
		updates["installation_date"] = *request.InstallationDate
	}
	if request.Status != nil {
		updates["status"] = *request.Status
	}

	if len(updates) == 0 {
		return nil
	}

	return r.db.Model(&entity.CableConnection{}).Where("id = ?", id).Updates(updates).Error
}

func (r *cableConnectionRepo) Delete(id uuid.UUID) error {
	return r.db.Delete(&entity.CableConnection{}, "id = ?", id).Error
}

// BulkCreate creates multiple cable connections in a single transaction
func (r *cableConnectionRepo) BulkCreate(connections []entity.CableConnection) ([]entity.CableConnection, []error) {
	var errors []error

	// Use transaction for bulk insert
	err := r.db.Transaction(func(tx *gorm.DB) error {
		if err := tx.CreateInBatches(connections, 50).Error; err != nil {
			return err
		}
		return nil
	})

	if err != nil {
		errors = append(errors, err)
		return nil, errors
	}

	return connections, nil
}

// BulkUpdate updates multiple cable connections with the same values
func (r *cableConnectionRepo) BulkUpdate(ids []uuid.UUID, updates req.UpdateCableConnectionDTO) (int64, error) {
	updateMap := make(map[string]interface{})

	if updates.FromDeviceID != nil {
		updateMap["from_device_id"] = *updates.FromDeviceID
	}
	if updates.ToDeviceID != nil {
		updateMap["to_device_id"] = *updates.ToDeviceID
	}
	if updates.CableLength != nil {
		updateMap["cable_length"] = *updates.CableLength
	}
	if updates.CableType != nil {
		updateMap["cable_type"] = *updates.CableType
	}
	if updates.BranchingType != nil {
		updateMap["branching_type"] = *updates.BranchingType
	}
	if updates.InstallationDate != nil {
		updateMap["installation_date"] = *updates.InstallationDate
	}
	if updates.Status != nil {
		updateMap["status"] = *updates.Status
	}

	if len(updateMap) == 0 {
		return 0, nil
	}

	result := r.db.Model(&entity.CableConnection{}).Where("id IN ?", ids).Updates(updateMap)
	return result.RowsAffected, result.Error
}

// BulkDelete deletes multiple cable connections
func (r *cableConnectionRepo) BulkDelete(ids []uuid.UUID) (int64, error) {
	result := r.db.Delete(&entity.CableConnection{}, "id IN ?", ids)
	return result.RowsAffected, result.Error
}

func (r *cableConnectionRepo) SearchWithPagination(request req.CableConnectionSearchDTO) ([]entity.CableConnection, int, error) {
	query := r.db.Model(&entity.CableConnection{}).Preload("FromDevice").Preload("ToDevice")

	// Apply filters
	if request.CableType != nil && *request.CableType != "" {
		query = query.Where("cable_type = ?", *request.CableType)
	}
	if request.Status != nil && *request.Status != "" {
		query = query.Where("status = ?", *request.Status)
	}
	if request.DeviceID != nil {
		query = query.Where("from_device_id = ? OR to_device_id = ?", *request.DeviceID, *request.DeviceID)
	}
	if request.MinLength != nil {
		query = query.Where("cable_length >= ?", *request.MinLength)
	}
	if request.MaxLength != nil {
		query = query.Where("cable_length <= ?", *request.MaxLength)
	}
	if request.BranchingType != nil && *request.BranchingType != "" {
		query = query.Where("branching_type = ?", *request.BranchingType)
	}
	if request.InstallationDateFrom != nil {
		query = query.Where("installation_date >= ?", *request.InstallationDateFrom)
	}
	if request.InstallationDateTo != nil {
		query = query.Where("installation_date <= ?", *request.InstallationDateTo)
	}

	// Get total count
	var total int64
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}

	// Apply pagination
	offset := (request.Page - 1) * request.PerPage
	query = query.Offset(offset).Limit(request.PerPage)

	// Apply sorting
	orderBy := "created_at DESC"
	if request.SortBy != nil && *request.SortBy != "" {
		direction := "ASC"
		if request.SortDirection != nil && *request.SortDirection == "desc" {
			direction = "DESC"
		}
		orderBy = *request.SortBy + " " + direction
	}
	query = query.Order(orderBy)

	var connections []entity.CableConnection
	err := query.Find(&connections).Error

	return connections, int(total), err
}

func (r *cableConnectionRepo) GetByDeviceID(deviceID uuid.UUID) ([]entity.CableConnection, error) {
	var connections []entity.CableConnection
	err := r.db.Preload("FromDevice").Preload("ToDevice").
		Where("from_device_id = ? OR to_device_id = ?", deviceID, deviceID).
		Find(&connections).Error
	return connections, err
}

func (r *cableConnectionRepo) GetLengthDistribution(cableType string) (res.CableLengthDistributionResponse, error) {
	query := r.db.Model(&entity.CableConnection{})

	if cableType != "" {
		query = query.Where("cable_type = ?", cableType)
	}

	var distribution res.CableLengthDistributionResponse

	// Get length ranges
	err := query.Select(`
		COUNT(CASE WHEN cable_length < 100 THEN 1 END) as under_100m,
		COUNT(CASE WHEN cable_length >= 100 AND cable_length < 500 THEN 1 END) as from_100_500m,
		COUNT(CASE WHEN cable_length >= 500 AND cable_length < 1000 THEN 1 END) as from_500_1000m,
		COUNT(CASE WHEN cable_length >= 1000 AND cable_length < 5000 THEN 1 END) as from_1_5km,
		COUNT(CASE WHEN cable_length >= 5000 THEN 1 END) as above_5km,
		AVG(cable_length) as average_length,
		MIN(cable_length) as min_length,
		MAX(cable_length) as max_length,
		COUNT(*) as total_connections
	`).Scan(&distribution).Error

	return distribution, err
}

func (r *cableConnectionRepo) GetTypeAnalytics() (res.CableTypeAnalyticsResponse, error) {
	var typeStats []res.CableTypeStats

	err := r.db.Model(&entity.CableConnection{}).
		Select("cable_type, COUNT(*) as count, AVG(cable_length) as average_length, SUM(cable_length) as total_length").
		Group("cable_type").
		Scan(&typeStats).Error

	if err != nil {
		return res.CableTypeAnalyticsResponse{}, err
	}

	// Get total metrics
	var totalConnections int64
	var totalLength float64

	r.db.Model(&entity.CableConnection{}).Count(&totalConnections)
	r.db.Model(&entity.CableConnection{}).Select("SUM(cable_length)").Scan(&totalLength)

	response := res.CableTypeAnalyticsResponse{
		TypeStats:        typeStats,
		TotalConnections: int(totalConnections),
		TotalLength:      totalLength,
	}

	return response, nil
}

func (r *cableConnectionRepo) GetStatusSummary() (res.CableStatusSummaryResponse, error) {
	var statusStats []res.CableStatusStats

	err := r.db.Model(&entity.CableConnection{}).
		Select("status, COUNT(*) as count").
		Group("status").
		Scan(&statusStats).Error

	if err != nil {
		return res.CableStatusSummaryResponse{}, err
	}

	var totalConnections int64
	r.db.Model(&entity.CableConnection{}).Count(&totalConnections)

	response := res.CableStatusSummaryResponse{
		StatusStats:      statusStats,
		TotalConnections: int(totalConnections),
	}

	return response, nil
}

func (r *cableConnectionRepo) FindPossibleRoutes(fromDeviceID, toDeviceID uuid.UUID) ([]res.RouteConnectionResponse, error) {
	var routes []res.RouteConnectionResponse

	// Find intermediate devices that could be used for routing
	query := `
		WITH RECURSIVE route_finder AS (
			-- Start with direct connections from source device
			SELECT 
				cc.id,
				cc.from_device_id,
				cc.to_device_id,
				cc.cable_length,
				cc.cable_type,
				1 as hop_count,
				ARRAY[cc.from_device_id, cc.to_device_id] as path
			FROM cable_connections cc
			WHERE cc.from_device_id = ? AND cc.status = 'active'
			
			UNION ALL
			
			-- Recursively find connections through intermediate devices
			SELECT 
				cc.id,
				cc.from_device_id,
				cc.to_device_id,
				rf.cable_length + cc.cable_length,
				cc.cable_type,
				rf.hop_count + 1,
				rf.path || cc.to_device_id
			FROM cable_connections cc
			JOIN route_finder rf ON cc.from_device_id = rf.to_device_id
			WHERE cc.status = 'active' 
				AND rf.hop_count < 5  -- Limit to 5 hops
				AND NOT (cc.to_device_id = ANY(rf.path))  -- Avoid loops
		)
		SELECT DISTINCT
			rf.from_device_id,
			rf.to_device_id,
			rf.cable_length as total_length,
			rf.hop_count,
			rf.path
		FROM route_finder rf
		WHERE rf.to_device_id = ?
		ORDER BY rf.hop_count, rf.cable_length
		LIMIT 10
	`

	err := r.db.Raw(query, fromDeviceID, toDeviceID).Scan(&routes).Error
	return routes, err
}

func (r *cableConnectionRepo) TracePath(fromDeviceID, toDeviceID uuid.UUID, maxHops int) (res.CablePathResponse, error) {
	if maxHops <= 0 {
		maxHops = 10
	}

	var pathResponse res.CablePathResponse

	// Find the shortest path using a recursive CTE
	query := `
		WITH RECURSIVE path_trace AS (
			SELECT 
				cc.id,
				cc.from_device_id,
				cc.to_device_id,
				cc.cable_length,
				cc.cable_type,
				1 as hop_count,
				ARRAY[cc.from_device_id] as visited_devices,
				ARRAY[cc.id] as connection_path
			FROM cable_connections cc
			WHERE cc.from_device_id = ? AND cc.status = 'active'
			
			UNION ALL
			
			SELECT 
				cc.id,
				cc.from_device_id,
				cc.to_device_id,
				pt.cable_length + cc.cable_length,
				cc.cable_type,
				pt.hop_count + 1,
				pt.visited_devices || cc.from_device_id,
				pt.connection_path || cc.id
			FROM cable_connections cc
			JOIN path_trace pt ON cc.from_device_id = pt.to_device_id
			WHERE cc.status = 'active' 
				AND pt.hop_count < ?
				AND NOT (cc.from_device_id = ANY(pt.visited_devices))
		)
		SELECT 
			from_device_id,
			to_device_id,
			cable_length as total_length,
			hop_count,
			connection_path
		FROM path_trace
		WHERE to_device_id = ?
		ORDER BY hop_count, cable_length
		LIMIT 1
	`

	err := r.db.Raw(query, fromDeviceID, maxHops, toDeviceID).Scan(&pathResponse).Error
	return pathResponse, err
}

func (r *cableConnectionRepo) GetNetworkMap(deviceType, cableType, region string) (res.NetworkMapResponse, error) {
	query := r.db.Model(&entity.CableConnection{}).
		Select(`
			cc.id,
			cc.from_device_id,
			cc.to_device_id,
			cc.cable_length,
			cc.cable_type,
			cc.status,
			fd.device_code as from_device_code,
			fd.device_type as from_device_type,
			fd.latitude as from_latitude,
			fd.longitude as from_longitude,
			td.device_code as to_device_code,
			td.device_type as to_device_type,
			td.latitude as to_latitude,
			td.longitude as to_longitude
		`).
		Joins("cc").
		Joins("LEFT JOIN devices fd ON cc.from_device_id = fd.id").
		Joins("LEFT JOIN devices td ON cc.to_device_id = td.id").
		Where("cc.status = ?", "active")

	if deviceType != "" {
		query = query.Where("fd.device_type = ? OR td.device_type = ?", deviceType, deviceType)
	}
	if cableType != "" {
		query = query.Where("cc.cable_type = ?", cableType)
	}
	if region != "" {
		query = query.Where("fd.province = ? OR fd.city = ? OR td.province = ? OR td.city = ?",
			region, region, region, region)
	}

	var mapData []res.NetworkMapConnection
	err := query.Scan(&mapData).Error

	if err != nil {
		return res.NetworkMapResponse{}, err
	}

	// Calculate summary statistics
	var stats res.NetworkMapStats
	r.db.Model(&entity.CableConnection{}).
		Select("COUNT(*) as total_connections, AVG(cable_length) as average_length").
		Where("status = ?", "active").
		Scan(&stats)

	response := res.NetworkMapResponse{
		Connections: mapData,
		Stats:       stats,
	}

	return response, nil
}

func (r *cableConnectionRepo) UpdateStatus(id uuid.UUID, status string, notes *string) error {
	updates := map[string]interface{}{
		"status": status,
	}

	if notes != nil {
		updates["notes"] = *notes
	}

	return r.db.Model(&entity.CableConnection{}).Where("id = ?", id).Updates(updates).Error
}

func (r *cableConnectionRepo) GetMaintenanceDue(days int) ([]res.MaintenanceItemResponse, error) {
	var maintenanceItems []res.MaintenanceItemResponse

	// Find connections that need maintenance based on installation date
	query := `
		SELECT 
			cc.id,
			cc.from_device_id,
			cc.to_device_id,
			cc.cable_length,
			cc.cable_type,
			cc.installation_date,
			cc.status,
			fd.device_code as from_device_code,
			td.device_code as to_device_code,
			EXTRACT(DAYS FROM (NOW() - cc.installation_date)) as days_since_installation
		FROM cable_connections cc
		LEFT JOIN devices fd ON cc.from_device_id = fd.id
		LEFT JOIN devices td ON cc.to_device_id = td.id
		WHERE cc.installation_date IS NOT NULL
			AND cc.installation_date < (NOW() - INTERVAL ? || ' days')
			AND cc.status = 'active'
		ORDER BY cc.installation_date ASC
	`

	err := r.db.Raw(query, days).Scan(&maintenanceItems).Error
	return maintenanceItems, err
}