package mysql

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	flightdata "wucher/internal/domain/flight_data"
	helicopterusage "wucher/internal/domain/helicopter_usage"
	"wucher/internal/shared/pkg/metricparse"
)

type HelicopterUsageRepository struct {
	db *gorm.DB
}

func NewHelicopterUsageRepository(db *gorm.DB) *HelicopterUsageRepository {
	return &HelicopterUsageRepository{db: db}
}

func (r *HelicopterUsageRepository) Create(ctx context.Context, row *helicopterusage.HelicopterUsage) error {
	return r.db.WithContext(ctx).Create(row).Error
}

func (r *HelicopterUsageRepository) Update(ctx context.Context, row *helicopterusage.HelicopterUsage) error {
	return r.db.WithContext(ctx).Save(row).Error
}

func (r *HelicopterUsageRepository) Delete(ctx context.Context, id []byte, deletedBy []byte) error {
	if err := ensureNoReferenceBeforeDelete(ctx, r.db, "helicopter_usage", id); err != nil {
		return err
	}
	now := time.Now().UTC()
	updates := map[string]any{
		"deleted_at": now,
		"deleted_by": deletedBy,
		"updated_by": deletedBy,
	}
	return mapDeleteConstraintError(r.db.WithContext(ctx).
		Model(&helicopterusage.HelicopterUsage{}).
		Where("id = ? AND deleted_at IS NULL", id).
		Updates(updates).Error)
}

func (r *HelicopterUsageRepository) RefreshAll(ctx context.Context) error {
	now := time.Now().UTC()
	existing := map[string]helicopterusage.HelicopterUsage{}
	{
		var rows []helicopterusage.HelicopterUsage
		if err := r.db.WithContext(ctx).
			Select("helicopter_usage.*").
			Where("helicopter_usage.deleted_at IS NULL").
			Find(&rows).Error; err != nil {
			return err
		}
		for i := range rows {
			key, err := helicopterUsageKey(rows[i].HelicopterID)
			if err != nil {
				continue
			}
			existing[key] = rows[i]
		}
	}

	helicopterIDs, err := r.listActiveHelicopterIDs(ctx)
	if err != nil {
		return err
	}
	if len(helicopterIDs) == 0 {
		return nil
	}

	flightStats, err := r.flightStatsByHelicopter(ctx)
	if err != nil {
		return err
	}
	reportStats, err := r.reportStatsByHelicopter(ctx)
	if err != nil {
		return err
	}

	for _, helicopterID := range helicopterIDs {
		key, _ := helicopterUsageKey(helicopterID)
		row := &helicopterusage.HelicopterUsage{
			HelicopterID:         append([]byte(nil), helicopterID...),
			TotalLanding:         flightStats[key].TotalLanding,
			TotalAirframeHours:   flightStats[key].TotalAirframeHours,
			TotalAirframeCycles:  flightStats[key].TotalAirframeCycles,
			TotalEngine1Hours:    flightStats[key].TotalAirframeHours,
			TotalEngine2Hours:    flightStats[key].TotalAirframeHours,
			TotalEngine1GpcNgN1:  reportStats[key].TotalEngine1GpcNgN1,
			TotalEngine1PtcNfN2:  reportStats[key].TotalEngine1PtcNfN2,
			TotalEngine2GpcNgN1:  reportStats[key].TotalEngine2GpcNgN1,
			TotalEngine2PtcNfN2:  reportStats[key].TotalEngine2PtcNfN2,
			TotalFlightReport:    reportStats[key].TotalFlightReport,
			TotalHookRelease:     flightStats[key].TotalHookRelease,
			TotalRotorBrakeCycle: flightStats[key].TotalRotorBrakeCycle,
		}
		if ex, ok := existing[key]; ok {
			row.ID = ex.ID
			row.CreatedAt = ex.CreatedAt
			row.CreatedBy = ex.CreatedBy
			row.UpdatedBy = ex.UpdatedBy
			row.TotalEngine1Ccc = ex.TotalEngine1Ccc
			row.TotalEngine2Ccc = ex.TotalEngine2Ccc

			row.ManualAirframeHours = ex.ManualAirframeHours
			row.ManualAirframeCycles = ex.ManualAirframeCycles
			row.ManualEngine1Hours = ex.ManualEngine1Hours
			row.ManualEngine1GpcNgN1 = ex.ManualEngine1GpcNgN1
			row.ManualEngine1PtcNfN2 = ex.ManualEngine1PtcNfN2
			row.ManualEngine2Hours = ex.ManualEngine2Hours
			row.ManualEngine2GpcNgN1 = ex.ManualEngine2GpcNgN1
			row.ManualEngine2PtcNfN2 = ex.ManualEngine2PtcNfN2
			row.ManualLanding = ex.ManualLanding
			row.ManualFlightReport = ex.ManualFlightReport
			row.ManualHookRelease = ex.ManualHookRelease
			row.ManualRotorBrakeCycle = ex.ManualRotorBrakeCycle

			row.TotalAirframeHours += ex.ManualAirframeHours
			row.TotalAirframeCycles += ex.ManualAirframeCycles
			row.TotalEngine1Hours += ex.ManualEngine1Hours
			row.TotalEngine1GpcNgN1 += ex.ManualEngine1GpcNgN1
			row.TotalEngine1PtcNfN2 += ex.ManualEngine1PtcNfN2
			row.TotalEngine2Hours += ex.ManualEngine2Hours
			row.TotalEngine2GpcNgN1 += ex.ManualEngine2GpcNgN1
			row.TotalEngine2PtcNfN2 += ex.ManualEngine2PtcNfN2
			row.TotalLanding += ex.ManualLanding
			row.TotalFlightReport += ex.ManualFlightReport
			row.TotalHookRelease += ex.ManualHookRelease
			row.TotalRotorBrakeCycle += ex.ManualRotorBrakeCycle
		} else {
			row.CreatedAt = now
		}
		row.UpdatedAt = now
		if err := r.upsertByHelicopterID(ctx, row); err != nil {
			return err
		}
	}
	return nil
}

func (r *HelicopterUsageRepository) GetByID(ctx context.Context, id []byte) (*helicopterusage.HelicopterUsage, error) {
	var row helicopterusage.HelicopterUsage
	err := r.db.WithContext(ctx).
		Preload("Helicopter").
		Where("id = ? AND deleted_at IS NULL", id).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

func (r *HelicopterUsageRepository) GetByHelicopterID(ctx context.Context, helicopterID []byte) (*helicopterusage.HelicopterUsage, error) {
	var row helicopterusage.HelicopterUsage
	err := r.db.WithContext(ctx).
		Preload("Helicopter").
		Where("helicopter_id = ? AND deleted_at IS NULL", helicopterID).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

type helicopterUsageFlightStats struct {
	TotalLanding         float64
	TotalAirframeHours   float64
	TotalAirframeCycles  float64
	TotalHookRelease     float64
	TotalRotorBrakeCycle float64
}

type helicopterUsageReportStats struct {
	TotalFlightReport   float64
	TotalEngine1GpcNgN1 float64
	TotalEngine1PtcNfN2 float64
	TotalEngine2GpcNgN1 float64
	TotalEngine2PtcNfN2 float64
}

func (r *HelicopterUsageRepository) listActiveHelicopterIDs(ctx context.Context) ([][]byte, error) {
	type row struct {
		ID []byte `gorm:"column:id"`
	}
	rows := make([]row, 0)
	if err := r.db.WithContext(ctx).
		Table("helicopters").
		Select("id").
		Where("is_active = ?", true).
		Order("created_at ASC, id ASC").
		Scan(&rows).Error; err != nil {
		return nil, err
	}
	out := make([][]byte, 0, len(rows))
	for i := range rows {
		if len(rows[i].ID) != 16 {
			continue
		}
		out = append(out, append([]byte(nil), rows[i].ID...))
	}
	return out, nil
}

func (r *HelicopterUsageRepository) flightStatsByHelicopter(ctx context.Context) (map[string]helicopterUsageFlightStats, error) {
	type row struct {
		HelicopterID         []byte `gorm:"column:helicopter_id"`
		TotalLanding         int64  `gorm:"column:total_landing"`
		TotalAirframeNS      int64  `gorm:"column:total_airframe_ns"`
		TotalAirframeCycles  int64  `gorm:"column:total_airframe_cycles"`
		TotalHookRelease     int64  `gorm:"column:total_hook_release"`
		TotalRotorBrakeCycle int64  `gorm:"column:total_rotor_brake_cycle"`
	}
	rows := make([]row, 0)
	err := r.db.WithContext(ctx).
		Table("flight_data fd").
		Select(`
			ra.helicopter_id,
			COALESCE(SUM(fd.landing_count), 0) AS total_landing,
			COALESCE(SUM(fd.duration), 0) AS total_airframe_ns,
			COUNT(fd.id) AS total_airframe_cycles,
			COALESCE(SUM(fd.hook_releases), 0) AS total_hook_release,
			COALESCE(SUM(fd.rotor_brake_cycle), 0) AS total_rotor_brake_cycle
		`).
		Joins("JOIN missions m ON m.id = fd.mission_id AND m.deleted_at IS NULL").
		Joins("JOIN flights f ON f.id = m.flight_id AND f.deleted_at IS NULL").
		Joins("JOIN takeover_acs ta ON ta.id = f.takeover_ac_id AND ta.deleted_at IS NULL").
		Joins("JOIN reserve_acs ra ON ra.id = ta.reserve_ac_id AND ra.deleted_at IS NULL").
		Where("fd.deleted_at IS NULL AND fd.status = ?", flightdata.StatusCompleted).
		Group("ra.helicopter_id").
		Scan(&rows).Error
	if err != nil {
		return nil, err
	}
	out := make(map[string]helicopterUsageFlightStats, len(rows))
	for i := range rows {
		key, err := helicopterUsageKey(rows[i].HelicopterID)
		if err != nil {
			continue
		}
		out[key] = helicopterUsageFlightStats{
			TotalLanding:         float64(rows[i].TotalLanding),
			TotalAirframeHours:   float64(rows[i].TotalAirframeNS) / float64(time.Hour),
			TotalAirframeCycles:  float64(rows[i].TotalAirframeCycles),
			TotalHookRelease:     float64(rows[i].TotalHookRelease),
			TotalRotorBrakeCycle: float64(rows[i].TotalRotorBrakeCycle),
		}
	}
	return out, nil
}

func (r *HelicopterUsageRepository) reportStatsByHelicopter(ctx context.Context) (map[string]helicopterUsageReportStats, error) {
	type row struct {
		HelicopterID []byte  `gorm:"column:helicopter_id"`
		Engine1GpcN1 *string `gorm:"column:engine1_gpc_n1"`
		Engine1PtcN2 *string `gorm:"column:engine1_ptc_n2"`
		Engine2GpcN1 *string `gorm:"column:engine2_gpc_n1"`
		Engine2PtcN2 *string `gorm:"column:engine2_ptc_n2"`
	}
	rows := make([]row, 0)
	if err := r.db.WithContext(ctx).
		Table("fm_reports").
		Select("helicopter_id, engine1_gpc_n1, engine1_ptc_n2, engine2_gpc_n1, engine2_ptc_n2").
		Where("deleted_at IS NULL AND helicopter_id IS NOT NULL").
		Order("helicopter_id ASC, created_at ASC, id ASC").
		Scan(&rows).Error; err != nil {
		return nil, err
	}
	out := make(map[string]helicopterUsageReportStats)
	for i := range rows {
		key, err := helicopterUsageKey(rows[i].HelicopterID)
		if err != nil {
			continue
		}
		acc := out[key]
		acc.TotalFlightReport++
		acc.TotalEngine1GpcNgN1 += parseNumericMetric(rows[i].Engine1GpcN1)
		acc.TotalEngine1PtcNfN2 += parseNumericMetric(rows[i].Engine1PtcN2)
		acc.TotalEngine2GpcNgN1 += parseNumericMetric(rows[i].Engine2GpcN1)
		acc.TotalEngine2PtcNfN2 += parseNumericMetric(rows[i].Engine2PtcN2)
		out[key] = acc
	}
	return out, nil
}

func (r *HelicopterUsageRepository) upsertByHelicopterID(ctx context.Context, row *helicopterusage.HelicopterUsage) error {
	return r.db.WithContext(ctx).
		Clauses(clause.OnConflict{
			Columns: []clause.Column{{Name: "helicopter_id"}},
			DoUpdates: clause.AssignmentColumns([]string{
				"total_landing",
				"total_airframe_hours",
				"total_airframe_cycles",
				"total_engine_1_hours",
				"total_engine_1_gpc_ng_n1",
				"total_engine_1_ptc_nf_n2",
				"total_engine_2_hours",
				"total_engine_2_gpc_ng_n1",
				"total_engine_2_ptc_nf_n2",
				"total_flight_report",
				"total_hook_release",
				"total_rotor_brake_cycle",
				"updated_at",
				"updated_by",
			}),
		}).
		Create(row).Error
}

func helicopterUsageKey(id []byte) (string, error) {
	if len(id) != 16 {
		return "", fmt.Errorf("invalid helicopter id")
	}
	return string(id), nil
}

func parseNumericMetric(v *string) float64 {
	return metricparse.Parse(v)
}

// FlightStatsByFlightID aggregates one flight's completed flight_data — the "today"
// contribution of an FM report. Uses the same SUM/COUNT definitions as the cumulative
// totals so prev = total - today reconciles exactly.
func (r *HelicopterUsageRepository) FlightStatsByFlightID(ctx context.Context, flightID []byte) (helicopterusage.FlightDayStats, error) {
	if len(flightID) != 16 {
		return helicopterusage.FlightDayStats{}, nil
	}
	var row struct {
		TotalLanding         int64 `gorm:"column:total_landing"`
		TotalAirframeNS      int64 `gorm:"column:total_airframe_ns"`
		TotalAirframeCycles  int64 `gorm:"column:total_airframe_cycles"`
		TotalHookRelease     int64 `gorm:"column:total_hook_release"`
		TotalRotorBrakeCycle int64 `gorm:"column:total_rotor_brake_cycle"`
	}
	err := r.db.WithContext(ctx).
		Table("flight_data fd").
		Select(`
			COALESCE(SUM(fd.landing_count), 0) AS total_landing,
			COALESCE(SUM(fd.duration), 0) AS total_airframe_ns,
			COUNT(fd.id) AS total_airframe_cycles,
			COALESCE(SUM(fd.hook_releases), 0) AS total_hook_release,
			COALESCE(SUM(fd.rotor_brake_cycle), 0) AS total_rotor_brake_cycle
		`).
		Joins("JOIN missions m ON m.id = fd.mission_id AND m.deleted_at IS NULL").
		Where("fd.deleted_at IS NULL AND fd.status = ? AND m.flight_id = ?", flightdata.StatusCompleted, flightID).
		Scan(&row).Error
	if err != nil {
		return helicopterusage.FlightDayStats{}, err
	}
	return helicopterusage.FlightDayStats{
		AirframeHours:   float64(row.TotalAirframeNS) / float64(time.Hour),
		AirframeCycles:  float64(row.TotalAirframeCycles),
		Landing:         float64(row.TotalLanding),
		HookRelease:     float64(row.TotalHookRelease),
		RotorBrakeCycle: float64(row.TotalRotorBrakeCycle),
	}, nil
}

func (r *HelicopterUsageRepository) List(ctx context.Context, filter, sort string, limit, offset int) ([]helicopterusage.HelicopterUsage, int64, error) {
	rows := make([]helicopterusage.HelicopterUsage, 0)
	var total int64

	base := r.db.WithContext(ctx).
		Model(&helicopterusage.HelicopterUsage{}).
		Joins("LEFT JOIN helicopters h ON h.id = helicopter_usage.helicopter_id AND h.deleted_at IS NULL").
		Where("helicopter_usage.deleted_at IS NULL")

	if strings.TrimSpace(filter) != "" {
		like := "%" + strings.ToLower(strings.TrimSpace(filter)) + "%"
		base = base.Where(
			"LOWER(HEX(helicopter_usage.id)) LIKE ? OR LOWER(HEX(helicopter_usage.helicopter_id)) LIKE ? OR LOWER(h.designation) LIKE ? OR LOWER(h.identifier) LIKE ?",
			like, like, like, like,
		)
	}

	query := base
	if strings.TrimSpace(sort) != "" {
		query = query.Order(sort)
	} else {
		query = query.Order("helicopter_usage.created_at DESC")
	}

	if err := base.Count(&total).Error; err != nil {
		return nil, 0, err
	}
	if limit > 0 {
		query = query.Limit(limit).Offset(offset)
	}
	if err := query.Preload("Helicopter").Find(&rows).Error; err != nil {
		return nil, 0, err
	}
	return rows, total, nil
}