package mysql

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

	"gorm.io/gorm"

	flightdata "wucher/internal/domain/flight_data"
	"wucher/internal/domain/mission"
	"wucher/internal/shared/pkg/txctx"
)

type MissionRepository struct {
	db *gorm.DB
}

func NewMissionRepository(db *gorm.DB) *MissionRepository {
	return &MissionRepository{db: db}
}

func (r *MissionRepository) Create(ctx context.Context, row *mission.Mission) error {
	normalizeMissionTimeForDB(row)
	if err := txctx.DB(ctx, r.db).Create(row).Error; err != nil {
		if errors.Is(err, gorm.ErrDuplicatedKey) {
			return mission.ErrDuplicateFlight
		}
		return err
	}
	return nil
}

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

// MaxCodeSeqByPrefix returns the highest numeric suffix among mission codes that
// start with prefix (e.g. "HEMS-26-"), or 0 when none exist. Used to compute the
// next per-type, per-year sequence number.
func (r *MissionRepository) MaxCodeSeqByPrefix(ctx context.Context, prefix string) (int, error) {
	var maxSeq *int
	err := txctx.DB(ctx, r.db).
		Model(&mission.Mission{}).
		Where("code LIKE ? AND deleted_at IS NULL", prefix+"%").
		Select("MAX(CAST(SUBSTRING_INDEX(code, '-', -1) AS UNSIGNED))").
		Scan(&maxSeq).Error
	if err != nil {
		return 0, err
	}
	if maxSeq == nil {
		return 0, nil
	}
	return *maxSeq, nil
}

func (r *MissionRepository) AttachFile(ctx context.Context, missionID []byte, fileAttachmentID []byte) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var missionRow mission.Mission
		if err := tx.Where("id = ? AND deleted_at IS NULL", missionID).
			Take(&missionRow).Error; err != nil {
			return err
		}
		var existing mission.MissionFile
		err := tx.Where("mission_id = ? AND file_attachment_id = ?", missionID, fileAttachmentID).
			Take(&existing).Error
		if err == nil {
			return nil
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}
		return tx.Create(&mission.MissionFile{
			MissionID:        append([]byte(nil), missionID...),
			FileAttachmentID: append([]byte(nil), fileAttachmentID...),
		}).Error
	})
}

func (r *MissionRepository) DetachFile(ctx context.Context, missionID []byte, fileAttachmentID []byte) error {
	return r.db.WithContext(ctx).
		Where("mission_id = ? AND file_attachment_id = ?", missionID, fileAttachmentID).
		Delete(&mission.MissionFile{}).Error
}

func (r *MissionRepository) CreateCategory(ctx context.Context, row *mission.MissionCategory) error {
	return r.db.WithContext(ctx).Create(row).Error
}

func (r *MissionRepository) UpdateFlightDataIDByID(ctx context.Context, missionID, flightDataID, updatedBy []byte) error {
	patch := map[string]any{
		"flight_data_id": flightDataID,
	}
	if len(updatedBy) == 16 {
		patch["updated_by"] = updatedBy
	}
	res := txctx.DB(ctx, r.db).
		Model(&mission.Mission{}).
		Where("id = ? AND deleted_at IS NULL", missionID).
		Updates(patch)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *MissionRepository) UpdateByID(ctx context.Context, missionID []byte, missionType string, missionCategoryID []byte, subtypeID []byte, note string, updatedBy []byte) error {
	patch := map[string]any{
		"type":                missionType,
		"mission_category_id": missionCategoryID,
		"subtype_id":          nil,
		"note":                strings.TrimSpace(note),
	}
	if len(subtypeID) == 16 {
		patch["subtype_id"] = subtypeID
	}
	if len(updatedBy) == 16 {
		patch["updated_by"] = updatedBy
	}

	res := r.db.WithContext(ctx).
		Model(&mission.Mission{}).
		Where("id = ? AND deleted_at IS NULL", missionID).
		Updates(patch)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *MissionRepository) DeleteByID(ctx context.Context, missionID []byte, deletedBy []byte) error {
	now := time.Now().UTC()
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		patch := map[string]any{
			"deleted_at": now,
			"deleted_by": deletedBy,
		}
		if len(deletedBy) == 16 {
			patch["updated_by"] = deletedBy
		}

		res := tx.Model(&mission.Mission{}).
			Where("id = ? AND deleted_at IS NULL", missionID).
			Updates(patch)
		if res.Error != nil {
			return res.Error
		}
		if res.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}

		fdPatch := map[string]any{
			"deleted_at": now,
			"deleted_by": deletedBy,
		}
		if len(deletedBy) == 16 {
			fdPatch["updated_by"] = deletedBy
		}

		if err := tx.Model(&flightdata.FlightData{}).
			Where("mission_id = ? AND deleted_at IS NULL", missionID).
			Updates(fdPatch).Error; err != nil {
			return err
		}

		return nil
	})
}

func (r *MissionRepository) DeleteByFlightID(ctx context.Context, flightID []byte, deletedBy []byte) error {
	now := time.Now().UTC()
	patch := map[string]any{
		"deleted_at": now,
		"deleted_by": deletedBy,
	}
	if len(deletedBy) == 16 {
		patch["updated_by"] = deletedBy
	}

	res := r.db.WithContext(ctx).
		Model(&mission.Mission{}).
		Where("flight_id = ? AND deleted_at IS NULL", flightID).
		Updates(patch)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *MissionRepository) ListByFlightID(ctx context.Context, flightID []byte) ([]mission.Mission, error) {
	rows := make([]mission.Mission, 0)
	err := r.db.WithContext(ctx).
		Select(r.statusSelectSQL()).
		Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
		Preload("MissionSubCategory").
		Preload("Flight").
		Preload("Files").
		Preload("Files.FileAttachment").
		Preload("Files.FileAttachment.File").
		Where("missions.flight_id = ? AND missions.deleted_at IS NULL", flightID).
		Order("missions.created_at ASC, missions.id ASC").
		Find(&rows).Error
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func (r *MissionRepository) ListByFlightIDs(ctx context.Context, flightIDs [][]byte) ([]mission.Mission, error) {
	if len(flightIDs) == 0 {
		return []mission.Mission{}, nil
	}
	rows := make([]mission.Mission, 0)
	err := r.db.WithContext(ctx).
		Select(r.statusSelectSQL()).
		Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
		Preload("MissionSubCategory").
		Preload("Flight").
		Preload("Files").
		Preload("Files.FileAttachment").
		Preload("Files.FileAttachment.File").
		Where("missions.flight_id IN ? AND missions.deleted_at IS NULL", flightIDs).
		Order("missions.flight_id ASC, missions.created_at ASC, missions.id ASC").
		Find(&rows).Error
	if err != nil {
		return nil, err
	}
	return rows, nil
}

func (r *MissionRepository) GetByID(ctx context.Context, missionID []byte) (*mission.Mission, error) {
	var row mission.Mission
	err := r.db.WithContext(ctx).
		Select(r.statusSelectSQL()).
		Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
		Preload("MissionSubCategory").
		Preload("Flight").
		Preload("Files").
		Preload("Files.FileAttachment").
		Preload("Files.FileAttachment.File").
		Where("missions.id = ? AND missions.deleted_at IS NULL", missionID).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &row, nil
}

func (r *MissionRepository) GetByFlightID(ctx context.Context, flightID []byte) (*mission.Mission, error) {
	var row mission.Mission
	err := r.db.WithContext(ctx).
		Select(r.statusSelectSQL()).
		Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
		Preload("MissionSubCategory").
		Preload("Flight").
		Preload("Files").
		Preload("Files.FileAttachment").
		Preload("Files.FileAttachment.File").
		Where("missions.flight_id = ? AND missions.deleted_at IS NULL", flightID).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &row, nil
}

func (r *MissionRepository) List(ctx context.Context, filter, sort string, flightID []byte, flightDataStatus string, limit, offset int) ([]mission.Mission, int64, error) {
	rows, total, _, err := r.listWithFilter(ctx, mission.ListFilter{
		Search:           filter,
		Sort:             sort,
		FlightID:         flightID,
		FlightDataStatus: flightDataStatus,
	}, limit, offset, false)
	return rows, total, err
}

func (r *MissionRepository) ListDatatable(ctx context.Context, filter mission.ListFilter, limit, offset int) ([]mission.Mission, int64, int64, error) {
	return r.listWithFilter(ctx, filter, limit, offset, true)
}

func (r *MissionRepository) statusSelectSQL() string {
	return `missions.*,
		COALESCE((
			SELECT flight_data.id
			FROM flight_data
			WHERE flight_data.mission_id = missions.id
				AND flight_data.deleted_at IS NULL
			ORDER BY flight_data.created_at DESC, flight_data.id DESC
			LIMIT 1
		), missions.flight_data_id) AS flight_data_id,
		` + r.flightDataStatusExprSQL() + ` AS flight_data_status`
}

func (r *MissionRepository) flightDataStatusExprSQL() string {
	completeCondition := `(
		flight_data.take_off IS NOT NULL
		AND flight_data.landing IS NOT NULL
		AND flight_data.duration > 0
		AND flight_data.red > 0
		AND flight_data.max_n1 > 0
		AND flight_data.max_n2 > 0
		AND flight_data.pax_count > 0
		AND flight_data.landing_count > 0
		AND flight_data.rotor_brake_cycle > 0
		AND COALESCE(flight_data.ticket_no, '') <> ''
		AND COALESCE(flight_data.engine, '') <> ''
		AND COALESCE(flight_data.delivery_note_number, '') <> ''
		AND COALESCE(flight_data.customer_name, '') <> ''
		AND flight_data.flight_plan_distance > 0
		AND flight_data.flight_plan_time > 0
		AND flight_data.flight_plan_true_course > 0
		AND flight_data.fuel_before_flight > 0
		AND flight_data.fuel_upload > 0
		AND flight_data.fuel_after_flight > 0
		AND flight_data.fuel_planning > 0
		AND COALESCE(flight_data.other_information, '') <> ''
	)`
	return `COALESCE((
		SELECT CASE
			WHEN COUNT(flight_data.id) = 0 THEN 'draft'
			WHEN SUM(CASE WHEN ` + completeCondition + ` THEN 1 ELSE 0 END) = 0 THEN 'draft'
			WHEN SUM(CASE WHEN ` + completeCondition + ` THEN 1 ELSE 0 END) = COUNT(flight_data.id) THEN 'completed'
			ELSE 'in_progress'
		END
		FROM flight_data
		WHERE flight_data.mission_id = missions.id
			AND flight_data.deleted_at IS NULL
	), 'draft')`
}

func (r *MissionRepository) listWithFilter(ctx context.Context, filter mission.ListFilter, limit, offset int, includeUnfilteredTotal bool) ([]mission.Mission, int64, int64, error) {
	rows := make([]mission.Mission, 0)
	var total int64
	var filteredTotal int64

	base := r.db.WithContext(ctx).
		Model(&mission.Mission{}).
		Joins("LEFT JOIN flights ON flights.id = missions.flight_id").
		Joins("LEFT JOIN mission_subcategories msc ON msc.id = missions.subtype_id").
		Where("missions.deleted_at IS NULL")

	if includeUnfilteredTotal {
		if err := base.Session(&gorm.Session{}).Count(&total).Error; err != nil {
			return nil, 0, 0, err
		}
	} else {
		total = 0
	}

	base = r.applyMissionListFilter(base, filter)

	countQuery := base.Session(&gorm.Session{})
	if err := countQuery.Count(&filteredTotal).Error; err != nil {
		return nil, 0, 0, err
	}

	query := base.Session(&gorm.Session{}).
		Select(r.statusSelectSQL()).
		Preload("MissionSubCategory").
		Preload("Flight").
		Preload("Files").
		Preload("Files.FileAttachment").
		Preload("Files.FileAttachment.File")
	if filter.Sort != "" {
		query = query.Order(filter.Sort)
	}
	if limit > 0 {
		query = query.Limit(limit).Offset(offset)
	}

	if err := query.Find(&rows).Error; err != nil {
		return nil, 0, 0, err
	}

	if !includeUnfilteredTotal {
		total = filteredTotal
	}
	return rows, total, filteredTotal, nil
}

func (r *MissionRepository) applyMissionListFilter(query *gorm.DB, filter mission.ListFilter) *gorm.DB {
	if filter.Search != "" {
		like := "%" + filter.Search + "%"
		query = query.Where(
			"missions.type LIKE ? OR flights.mission_code LIKE ? OR DATE_FORMAT(flights.date, '%Y-%m-%d') LIKE ?",
			like, like, like,
		)
	}
	if len(filter.FlightID) == 16 {
		query = query.Where("missions.flight_id = ?", filter.FlightID)
	}
	if filter.FlightDataStatus != "" {
		query = query.Where(r.flightDataStatusExprSQL()+" = ?", filter.FlightDataStatus)
	}
	if filter.MissionType != "" {
		query = query.Where("missions.type = ?", strings.ToUpper(strings.TrimSpace(filter.MissionType)))
	}
	if filter.StartDate != nil {
		query = query.Where("flights.date >= ?", filter.StartDate.UTC().Format("2006-01-02"))
	}
	if filter.EndDate != nil {
		query = query.Where("flights.date <= ?", filter.EndDate.UTC().Format("2006-01-02"))
	}
	if len(filter.PilotID) == 16 {
		query = query.Where(`
			EXISTS (
				SELECT 1
				FROM duty_rosters dr
				JOIN duty_roster_crews c ON c.roster_id = dr.id
				WHERE dr.flight_id = missions.flight_id
					AND dr.deleted_at IS NULL
					AND c.deleted_at IS NULL
					AND c.role_code = 'pilot'
					AND c.user_id = ?
			)`, filter.PilotID)
	}
	if len(filter.HelicopterID) == 16 {
		query = query.Where(`
			EXISTS (
				SELECT 1
				FROM takeover_acs ta
				JOIN reserve_acs ra ON ra.id = ta.reserve_ac_id
					WHERE ta.id = flights.takeover_ac_id
						AND ta.deleted_at IS NULL
						AND ra.deleted_at IS NULL
						AND ra.helicopter_id = ?
			)`, filter.HelicopterID)
	}
	return query
}

func normalizeMissionTimeForDB(row *mission.Mission) {
	if row == nil {
		return
	}
	row.StartTime = normalizeMissionDBClock(row.StartTime)
	row.EndTime = normalizeMissionDBClock(row.EndTime)
}

func normalizeMissionDBClock(raw string) string {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return ""
	}
	if len(trimmed) == 5 {
		return trimmed + ":00"
	}
	if len(trimmed) == 8 {
		return trimmed
	}
	if parsed, err := time.Parse("15:04", trimmed); err == nil {
		return parsed.Format("15:04:05")
	}
	if parsed, err := time.Parse("15:04:05", trimmed); err == nil {
		return parsed.Format("15:04:05")
	}
	return trimmed
}

func (r *MissionRepository) ListCategoriesWithSubCategories(ctx context.Context) ([]mission.MissionCategory, error) {
	var rows []mission.MissionCategory
	if err := r.db.WithContext(ctx).
		Preload("SubCategory", func(db *gorm.DB) *gorm.DB {
			return db.Order("sub_type ASC")
		}).
		Order("code_type ASC").
		Find(&rows).Error; err != nil {
		return nil, err
	}

	for i := range rows {
		rows[i].CodeType = strings.TrimSpace(rows[i].CodeType)
		rows[i].TypeName = strings.TrimSpace(rows[i].TypeName)
		for j := range rows[i].SubCategory {
			rows[i].SubCategory[j].SubCodeType = strings.TrimSpace(rows[i].SubCategory[j].SubCodeType)
			rows[i].SubCategory[j].SubTypeName = strings.TrimSpace(rows[i].SubCategory[j].SubTypeName)
			rows[i].SubCategory[j].TaskName = strings.TrimSpace(rows[i].SubCategory[j].TaskName)
		}
	}

	return rows, nil
}

func (r *MissionRepository) FindCategoryByCode(ctx context.Context, code string) (*mission.MissionCategory, error) {
	var row mission.MissionCategory
	err := r.db.WithContext(ctx).
		Where("code_type = ?", code).
		Take(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &row, nil
}

func (r *MissionRepository) SubCategoryBelongsToCategory(ctx context.Context, subCategoryID, categoryID []byte) (bool, error) {
	if len(subCategoryID) != 16 || len(categoryID) != 16 {
		return false, nil
	}
	var cnt int64
	if err := r.db.WithContext(ctx).
		Table("mission_subcategories").
		Where("id = ? AND mission_category_id = ?", subCategoryID, categoryID).
		Count(&cnt).Error; err != nil {
		return false, err
	}
	return cnt > 0, nil
}