package mysql

import (
	"context"
	"errors"
	"fmt"
	"strconv"
	"strings"

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

	"wucher/internal/domain/helicopter"
	"wucher/internal/shared/pkg/sortkey"
)

type HelicopterRepository struct {
	db            *gorm.DB
	hasUsersTable bool
}

func NewHelicopterRepository(db *gorm.DB) *HelicopterRepository {
	schema := newSchemaCache(db)
	return &HelicopterRepository{
		db:            db,
		hasUsersTable: schema.HasTable("users"),
	}
}

func (r *HelicopterRepository) Create(ctx context.Context, h *helicopter.Helicopter) error {
	requestedIsActive := h.IsActive
	db := r.db.WithContext(ctx)
	if err := db.Create(h).Error; err != nil {
		return err
	}
	return db.Model(&helicopter.Helicopter{}).Where("id = ?", h.ID).UpdateColumn("is_active", requestedIsActive).Error
}

func (r *HelicopterRepository) CreateWithDB(ctx context.Context, db *gorm.DB, h *helicopter.Helicopter) error {
	requestedIsActive := h.IsActive
	if err := db.WithContext(ctx).Create(h).Error; err != nil {
		return err
	}
	return db.WithContext(ctx).Model(&helicopter.Helicopter{}).Where("id = ?", h.ID).UpdateColumn("is_active", requestedIsActive).Error
}

func (r *HelicopterRepository) Transaction(ctx context.Context, fn func(tx *gorm.DB) error) error {
	return r.db.WithContext(ctx).Transaction(fn)
}

func (r *HelicopterRepository) Update(ctx context.Context, h *helicopter.Helicopter) error {
	// Persist only helicopter columns; avoid association auto-save side effects
	// when handler payload carries preloaded FotoAttachment relation.
	return r.db.WithContext(ctx).Omit(clause.Associations).Save(h).Error
}

func (r *HelicopterRepository) ExistsByIdentifier(ctx context.Context, identifier string, excludeID []byte) (bool, error) {
	db := r.db.WithContext(ctx).Model(&helicopter.Helicopter{}).
		Where("UPPER(TRIM(identifier)) = ?", strings.ToUpper(strings.TrimSpace(identifier)))
	if len(excludeID) == 16 {
		db = db.Where("id <> ?", excludeID)
	}
	var total int64
	if err := db.Count(&total).Error; err != nil {
		return false, err
	}
	return total > 0, nil
}

func (r *HelicopterRepository) Delete(ctx context.Context, id []byte) error {
	if err := ensureNoReferenceBeforeDelete(ctx, r.db, "helicopters", id); err != nil {
		return err
	}
	return mapDeleteConstraintError(r.db.WithContext(ctx).Delete(&helicopter.Helicopter{}, "id = ?", id).Error)
}

func (r *HelicopterRepository) GetByID(ctx context.Context, id []byte) (*helicopter.Helicopter, error) {
	var h helicopter.Helicopter
	err := r.withAuditUsers(r.db.WithContext(ctx)).
		Preload("FotoAttachment").
		Preload("FotoAttachment.File").
		Where("helicopters.id = ?", id).
		First(&h).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &h, err
}

func (r *HelicopterRepository) List(ctx context.Context, filter string, statuses []string, sort string, limit, offset int, groundedIDs [][]byte) ([]helicopter.Helicopter, int64, error) {
	var helicopters []helicopter.Helicopter
	var total int64

	base := r.withAuditUsers(r.db.WithContext(ctx))
	if filter != "" {
		like := "%" + filter + "%"
		base = base.Where("helicopters.designation LIKE ? OR helicopters.identifier LIKE ? OR helicopters.type LIKE ?", like, like, like)
	}
	base = r.applyStatusFilter(base, statuses, groundedIDs)

	query := base
	if sort != "" {
		query = query.Order(sort)
	} else {
		for _, clause := range sortkey.ActivePositiveSortClauses("helicopters", "is_active", "sortkey", "designation", false) {
			query = query.Order(clause)
		}
	}
	if err := base.Count(&total).Error; err != nil {
		return nil, 0, err
	}
	if limit > 0 {
		query = query.Limit(limit).Offset(offset)
	}
	query = query.Preload("FotoAttachment").Preload("FotoAttachment.File")
	if err := query.Find(&helicopters).Error; err != nil {
		return nil, 0, err
	}
	return helicopters, total, nil
}

// applyStatusFilter restricts query to helicopters whose derived operational
// status is in statuses. The conditions mirror helicopter.DeriveStatus: AOG
// (air_on_ground) outranks an active flight assignment ("booked"), and
// "available" means neither; "mcf" has no data source yet, so it matches
// nothing. Multiple statuses are OR-ed together. Returns query unchanged when
// no status filter applies.
func (r *HelicopterRepository) applyStatusFilter(query *gorm.DB, statuses []string, groundedIDs [][]byte) *gorm.DB {
	if len(statuses) == 0 {
		return query
	}

	// inUse mirrors IsInUse: the helicopter has a non-deleted flight assignment
	// reachable through takeover_acs.reserve_ac_id. A fresh subquery is built
	// per use to avoid sharing builder state.
	inUse := func() *gorm.DB {
		return r.db.
			Table("reserve_acs ra").
			Select("1").
			Joins("JOIN takeover_acs ta ON ta.reserve_ac_id = ra.id AND ta.deleted_at IS NULL").
			Joins("JOIN flights f ON f.takeover_ac_id = ta.id").
			Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = ra.inspection_id").
			Where("ra.helicopter_id = helicopters.id").
			Where("ra.deleted_at IS NULL AND f.deleted_at IS NULL AND afi.id IS NULL")
	}

	// A helicopter is AOG when its persisted air_on_ground flag is set OR it is
	// grounded by an open complaint (computed in Go from IsGrounding and passed in via
	// groundedIDs, since the read-time grounding is not persisted in air_on_ground).
	hasGrounded := len(groundedIDs) > 0

	group := r.db.Session(&gorm.Session{NewDB: true})
	seen := make(map[string]struct{}, len(statuses))
	for _, status := range statuses {
		if _, ok := seen[status]; ok {
			continue
		}
		seen[status] = struct{}{}
		switch status {
		case helicopter.StatusAOG:
			aog := r.db.Where("helicopters.air_on_ground = ?", true)
			if hasGrounded {
				aog = aog.Or("helicopters.id IN ?", groundedIDs)
			}
			group = group.Or(aog)
		case helicopter.StatusBooked:
			booked := r.db.Where("helicopters.air_on_ground = ?", false).Where("EXISTS (?)", inUse())
			if hasGrounded {
				booked = booked.Where("helicopters.id NOT IN ?", groundedIDs)
			}
			group = group.Or(booked)
		case helicopter.StatusAvailable:
			avail := r.db.Where("helicopters.air_on_ground = ?", false).Where("NOT EXISTS (?)", inUse())
			if hasGrounded {
				avail = avail.Where("helicopters.id NOT IN ?", groundedIDs)
			}
			group = group.Or(avail)
		case helicopter.StatusMCF:
			group = group.Or("1 = 0") // no data source yet
		}
	}
	return query.Where(group)
}

func (r *HelicopterRepository) NextReportNumber(ctx context.Context, id []byte) (string, int, error) {
	var reportNumber string
	var sequence int

	err := r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var h helicopter.Helicopter
		if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).Where("id = ?", id).First(&h).Error; err != nil {
			return err
		}

		identifier := strings.TrimSpace(h.Identifier)
		if identifier == "" {
			return errors.New("helicopter identifier is empty")
		}
		if base, _, ok := splitIdentifierBase(identifier); ok {
			identifier = base
		}

		next := h.ReportSequence + 1
		if err := tx.Model(&helicopter.Helicopter{}).Where("id = ?", h.ID).Update("report_sequence", next).Error; err != nil {
			return err
		}

		sequence = next
		reportNumber = fmt.Sprintf("%s-%04d", identifier, next)
		return nil
	})
	if err != nil {
		return "", 0, err
	}

	return reportNumber, sequence, nil
}

func (r *HelicopterRepository) IsInUse(ctx context.Context, id []byte) (bool, error) {
	if len(id) != 16 {
		return false, nil
	}
	var total int64
	err := r.db.WithContext(ctx).
		Table("reserve_acs ra").
		Joins("JOIN takeover_acs ta ON ta.reserve_ac_id = ra.id AND ta.deleted_at IS NULL").
		Joins("JOIN flights f ON f.takeover_ac_id = ta.id").
		Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = ra.inspection_id").
		Where("ra.helicopter_id = ? AND ra.deleted_at IS NULL AND f.deleted_at IS NULL AND afi.id IS NULL", id).
		Count(&total).Error
	return total > 0, err
}

func (r *HelicopterRepository) InUseByAircraftIDs(ctx context.Context, ids [][]byte) (map[string]bool, error) {
	out := make(map[string]bool)
	if len(ids) == 0 {
		return out, nil
	}

	filtered := make([][]byte, 0, len(ids))
	seen := make(map[string]struct{}, len(ids))
	for _, id := range ids {
		if len(id) != 16 {
			continue
		}
		key := string(id)
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		filtered = append(filtered, append([]byte(nil), id...))
	}
	if len(filtered) == 0 {
		return out, nil
	}

	type row struct {
		HelicopterID []byte `gorm:"column:helicopter_id"`
	}
	rows := make([]row, 0)
	if err := r.db.WithContext(ctx).
		Table("reserve_acs ra").
		Select("DISTINCT ra.helicopter_id").
		Joins("JOIN takeover_acs ta ON ta.reserve_ac_id = ra.id AND ta.deleted_at IS NULL").
		Joins("JOIN flights f ON f.takeover_ac_id = ta.id").
		Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = ra.inspection_id").
		Where("ra.deleted_at IS NULL AND f.deleted_at IS NULL AND afi.id IS NULL").
		Where("ra.helicopter_id IN ?", filtered).
		Scan(&rows).Error; err != nil {
		return nil, err
	}

	for i := range rows {
		out[string(rows[i].HelicopterID)] = true
	}
	return out, nil
}

// ActiveFlightIDByAircraftIDs returns, per helicopter, the id of its current active flight
// (a takeover flight with no after-flight inspection yet). Mirrors the InUseByAircraftIDs
// join so "in use" and "which flight" stay consistent. Only in-use helicopters appear in
// the map; the newest flight wins if several are open. Used by the fleet status page to
// create an EASA release scoped to the flight.
func (r *HelicopterRepository) ActiveFlightIDByAircraftIDs(ctx context.Context, ids [][]byte) (map[string][]byte, error) {
	out := make(map[string][]byte)
	if len(ids) == 0 {
		return out, nil
	}
	filtered := make([][]byte, 0, len(ids))
	seen := make(map[string]struct{}, len(ids))
	for _, id := range ids {
		if len(id) != 16 {
			continue
		}
		key := string(id)
		if _, ok := seen[key]; ok {
			continue
		}
		seen[key] = struct{}{}
		filtered = append(filtered, append([]byte(nil), id...))
	}
	if len(filtered) == 0 {
		return out, nil
	}

	type row struct {
		HelicopterID []byte `gorm:"column:helicopter_id"`
		FlightID     []byte `gorm:"column:flight_id"`
	}
	rows := make([]row, 0)
	if err := r.db.WithContext(ctx).
		Table("reserve_acs ra").
		Select("ra.helicopter_id AS helicopter_id, f.id AS flight_id").
		Joins("JOIN takeover_acs ta ON ta.reserve_ac_id = ra.id AND ta.deleted_at IS NULL").
		Joins("JOIN flights f ON f.takeover_ac_id = ta.id").
		Joins("LEFT JOIN after_flight_inspections afi ON afi.flight_inspection_id = ra.inspection_id").
		Where("ra.deleted_at IS NULL AND f.deleted_at IS NULL AND afi.id IS NULL").
		Where("ra.helicopter_id IN ?", filtered).
		Order("f.created_at DESC").
		Scan(&rows).Error; err != nil {
		return nil, err
	}

	for i := range rows {
		k := string(rows[i].HelicopterID)
		if _, ok := out[k]; !ok && len(rows[i].FlightID) == 16 {
			out[k] = rows[i].FlightID
		}
	}
	return out, nil
}

func splitIdentifierBase(raw string) (string, int, bool) {
	s := strings.TrimSpace(raw)
	idx := strings.LastIndex(s, "-")
	if idx <= 0 || idx+5 != len(s) {
		return "", 0, false
	}
	base := strings.TrimSpace(s[:idx])
	if base == "" {
		return "", 0, false
	}
	seq, err := strconv.Atoi(s[idx+1:])
	if err != nil || seq <= 0 {
		return "", 0, false
	}
	return base, seq, true
}

func (r *HelicopterRepository) withAuditUsers(db *gorm.DB) *gorm.DB {
	base := db.Model(&helicopter.Helicopter{})
	if !r.hasUsersTable {
		return base.Select("helicopters.*, '' AS created_by_name, '' AS updated_by_name")
	}
	return base.
		Select(`
				helicopters.*,
				COALESCE(
					NULLIF(TRIM(CONCAT(COALESCE(cu.first_name, ''), ' ', COALESCE(cu.last_name, ''))), ''),
					NULLIF(TRIM(cu.username), ''),
					NULLIF(TRIM(cu.email), ''),
					''
				) AS created_by_name,
				COALESCE(
					NULLIF(TRIM(CONCAT(COALESCE(uu.first_name, ''), ' ', COALESCE(uu.last_name, ''))), ''),
					NULLIF(TRIM(uu.username), ''),
					NULLIF(TRIM(uu.email), ''),
					''
				) AS updated_by_name
			`).
		Joins("LEFT JOIN users cu ON cu.id = helicopters.created_by").
		Joins("LEFT JOIN users uu ON uu.id = helicopters.updated_by")
}