package mysql

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

	"gorm.io/gorm"

	flightinspection "wucher/internal/domain/flight_inspection"
	"wucher/internal/domain/helicopter"
	reserveac "wucher/internal/domain/reserve_ac"
	"wucher/internal/shared/pkg/uuidv7"
	shareddto "wucher/internal/transport/http/dto/shared"
)

type ReserveAcRepository struct {
	db *gorm.DB
}

func NewReserveAcRepository(db *gorm.DB) *ReserveAcRepository {
	return &ReserveAcRepository{db: db}
}

func (r *ReserveAcRepository) Create(ctx context.Context, row *reserveac.ReserveAc) error {
	return r.db.WithContext(ctx).Create(row).Error
}

func (r *ReserveAcRepository) Update(ctx context.Context, row *reserveac.ReserveAc) error {
	return r.db.WithContext(ctx).Save(row).Error
}

func (r *ReserveAcRepository) Delete(ctx context.Context, id []byte, deletedBy []byte) error {
	now := time.Now().UTC()
	updates := map[string]any{
		"deleted_at": now,
		"deleted_by": deletedBy,
		"updated_by": deletedBy,
	}
	res := r.db.WithContext(ctx).
		Model(&reserveac.ReserveAc{}).
		Where("id = ? AND deleted_at IS NULL", id).
		Updates(updates)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *ReserveAcRepository) GetByID(ctx context.Context, id []byte) (*reserveac.ReserveAc, error) {
	var row reserveac.ReserveAc
	err := r.db.WithContext(ctx).
		Preload("Aircraft").
		Preload("Inspection").
		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 *ReserveAcRepository) GetActiveByInspectionID(ctx context.Context, inspectionID []byte) (*reserveac.ReserveAc, error) {
	if len(inspectionID) != 16 {
		return nil, nil
	}
	var row reserveac.ReserveAc
	err := r.db.WithContext(ctx).
		Preload("Aircraft").
		Preload("Inspection").
		Where("inspection_id = ? AND deleted_at IS NULL", inspectionID).
		Limit(1).
		Find(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	if len(row.ID) == 0 {
		return nil, nil
	}
	return &row, nil
}

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

	base := r.db.WithContext(ctx).Model(&reserveac.ReserveAc{}).Where("deleted_at IS NULL")
	if strings.TrimSpace(filter) != "" {
		like := "%" + strings.ToLower(strings.TrimSpace(filter)) + "%"
		base = base.Where(
			"LOWER(status) LIKE ? OR LOWER(HEX(id)) LIKE ? OR LOWER(HEX(helicopter_id)) LIKE ? OR LOWER(HEX(inspection_id)) LIKE ?",
			like, like, like, like,
		)
	}

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

func (r *ReserveAcRepository) AircraftExists(ctx context.Context, helicopterID []byte) (bool, error) {
	if len(helicopterID) != 16 {
		return false, nil
	}
	var total int64
	err := r.db.WithContext(ctx).
		Model(&helicopter.Helicopter{}).
		Where("id = ?", helicopterID).
		Count(&total).Error
	return total > 0, err
}

func (r *ReserveAcRepository) FlightInspectionExists(ctx context.Context, inspectionID []byte) (bool, error) {
	if len(inspectionID) != 16 {
		return false, nil
	}
	var total int64
	err := r.db.WithContext(ctx).
		Model(&flightinspection.FlightInspection{}).
		Where("id = ?", inspectionID).
		Count(&total).Error
	return total > 0, err
}

func (r *ReserveAcRepository) ExistsActiveByInspectionID(ctx context.Context, inspectionID []byte, excludeID []byte) (bool, error) {
	if len(inspectionID) != 16 {
		return false, nil
	}
	var total int64
	q := r.db.WithContext(ctx).
		Model(&reserveac.ReserveAc{}).
		Where("inspection_id = ? AND deleted_at IS NULL", inspectionID)
	if len(excludeID) == 16 {
		q = q.Where("id <> ?", excludeID)
	}
	if err := q.Count(&total).Error; err != nil {
		return false, err
	}
	return total > 0, nil
}

func (r *ReserveAcRepository) GetLastDailyInspectionByHelicopterID(ctx context.Context, helicopterID []byte) (*shareddto.LastDailyInspection, error) {
	if len(helicopterID) != 16 {
		return nil, nil
	}

	type row struct {
		ID        []byte    `gorm:"column:id"`
		Name      string    `gorm:"column:name"`
		ShortName string    `gorm:"column:short_name"`
		At        time.Time `gorm:"column:inspected_at"`
	}
	var out row
	err := r.db.WithContext(ctx).
		Table("reserve_acs ra").
		Select(`
			fi.id AS id,
			TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))) AS name,
			COALESCE(NULLIF(pp.short_name, ''), NULLIF(tp.short_name, ''), '') AS short_name,
			COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) AS inspected_at`).
		Joins("JOIN flight_inspections fi ON fi.id = ra.inspection_id").
		Joins("LEFT JOIN before_flight_inspections bfi ON bfi.flight_inspection_id = fi.id").
		Joins("LEFT JOIN flight_prep_checks fpc ON fpc.flight_inspection_id = fi.id").
		Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = fi.id").
		Joins(`JOIN users u ON u.id = CASE
			WHEN afi.updated_at IS NOT NULL THEN afi.updated_by
			WHEN fpc.updated_at IS NOT NULL THEN fpc.updated_by
			WHEN bfi.updated_at IS NOT NULL THEN bfi.updated_by
			ELSE NULL
		END`).
		Joins("LEFT JOIN pilot_profiles pp ON pp.user_id = u.id").
		Joins("LEFT JOIN technician_profiles tp ON tp.user_id = u.id").
		Where("ra.helicopter_id = ? AND ra.deleted_at IS NULL", helicopterID).
		Where("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) IS NOT NULL").
		Order("fi.inspection_date DESC").
		Order("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at) DESC").
		Limit(1).
		Take(&out).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &shareddto.LastDailyInspection{
		ID:        out.ID,
		Name:      strings.TrimSpace(out.Name),
		ShortName: strings.TrimSpace(out.ShortName),
		At:        out.At.UTC(),
	}, nil
}

func (r *ReserveAcRepository) GetLastDailyInspectionSummaryByHelicopterID(ctx context.Context, helicopterID []byte) (*shareddto.LastDailyInspectionSummary, error) {
	if len(helicopterID) != 16 {
		return nil, nil
	}

	type row struct {
		InspectionID       []byte    `gorm:"column:inspection_id"`
		InspectorID        []byte    `gorm:"column:inspector_id"`
		InspectorName      string    `gorm:"column:inspector_name"`
		InspectorLicenseNo string    `gorm:"column:inspector_license_no"`
		BaseID             []byte    `gorm:"column:base_id"`
		BaseName           string    `gorm:"column:base_name"`
		BaseAbbreviation   string    `gorm:"column:base_abbreviation"`
		BaseType           string    `gorm:"column:base_type"`
		InspectedAt        time.Time `gorm:"column:inspected_at"`
		CompletedCount     int       `gorm:"column:completed_count"`
		TotalCount         int       `gorm:"column:total_count"`
	}
	var out row
	err := r.db.WithContext(ctx).
		Table("reserve_acs ra").
		Select(`
			ra.inspection_id AS inspection_id,
			COALESCE(u.id, ra.updated_by) AS inspector_id,
			COALESCE(
				NULLIF(TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))), ''),
				NULLIF(TRIM(u.username), ''),
				NULLIF(TRIM(u.email), ''),
				''
			) AS inspector_name,
			COALESCE(NULLIF(pp.license_no, ''), NULLIF(tp.license_no, ''), '') AS inspector_license_no,
			ra.base_id AS base_id,
			COALESCE(TRIM(b.base), '') AS base_name,
			COALESCE(TRIM(b.base_abbreviation), '') AS base_abbreviation,
			COALESCE(NULLIF(LOWER(bc.key), ''), 'base') AS base_type,
			COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) AS inspected_at,
			(
				SELECT COUNT(*)
				FROM flight_inspection_file_checklists fic
				WHERE fic.flight_inspection_id = fi.id
			) AS total_count,
			(
				SELECT COUNT(*)
				FROM flight_inspection_file_checklists fic
				WHERE fic.flight_inspection_id = fi.id AND fic.is_done = 1
			) AS completed_count
		`).
		Joins("JOIN flight_inspections fi ON fi.id = ra.inspection_id").
		Joins("LEFT JOIN before_flight_inspections bfi ON bfi.flight_inspection_id = fi.id").
		Joins("LEFT JOIN flight_prep_checks fpc ON fpc.flight_inspection_id = fi.id").
		Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = fi.id").
		Joins(`LEFT JOIN users u ON u.id = CASE
			WHEN afi.updated_at IS NOT NULL THEN afi.updated_by
			WHEN fpc.updated_at IS NOT NULL THEN fpc.updated_by
			WHEN bfi.updated_at IS NOT NULL THEN bfi.updated_by
			ELSE ra.updated_by
		END`).
		Joins("LEFT JOIN pilot_profiles pp ON pp.user_id = u.id").
		Joins("LEFT JOIN technician_profiles tp ON tp.user_id = u.id").
		Joins("LEFT JOIN bases b ON b.id = ra.base_id AND b.deleted_at IS NULL").
		Joins("LEFT JOIN base_categories bc ON bc.id = b.base_category_id").
		Where("ra.helicopter_id = ? AND ra.deleted_at IS NULL", helicopterID).
		Where("COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) IS NOT NULL").
		Order("fi.inspection_date DESC, COALESCE(afi.updated_at, fpc.updated_at, bfi.updated_at, ra.updated_at) DESC").
		Limit(1).
		Take(&out).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	inspectionID, _ := uuidv7.BytesToString(out.InspectionID)
	inspectorID, _ := uuidv7.BytesToString(out.InspectorID)
	baseID, _ := uuidv7.BytesToString(out.BaseID)
	return &shareddto.LastDailyInspectionSummary{
		InspectionID:                      inspectionID,
		Status:                            "inspected_cleared",
		InspectorID:                       inspectorID,
		InspectorName:                     strings.TrimSpace(out.InspectorName),
		InspectorLicenseNo:                strings.TrimSpace(out.InspectorLicenseNo),
		BaseID:                            baseID,
		BaseName:                          strings.TrimSpace(out.BaseName),
		BaseAbbreviation:                  strings.TrimSpace(out.BaseAbbreviation),
		BaseType:                          strings.TrimSpace(out.BaseType),
		InspectedAt:                       out.InspectedAt.UTC(),
		InspectionFilesChecklistCompleted: out.CompletedCount,
		InspectionFilesChecklistTotal:     out.TotalCount,
	}, nil
}