package mysql

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

	"gorm.io/gorm"

	filemanager "wucher/internal/domain/file_manager"
	"wucher/internal/shared/pkg/uuidv7"
)

type FileManagerFileRepository struct {
	db *gorm.DB
}

func NewFileManagerFileRepository(db *gorm.DB) *FileManagerFileRepository {
	return &FileManagerFileRepository{db: db}
}

func (r *FileManagerFileRepository) Create(ctx context.Context, row *filemanager.File) error {
	return r.db.WithContext(ctx).Create(row).Error
}

func (r *FileManagerFileRepository) Update(ctx context.Context, row *filemanager.File) error {
	return r.db.WithContext(ctx).Save(row).Error
}

func (r *FileManagerFileRepository) TransitionStatus(ctx context.Context, params filemanager.FileStatusTransitionParams) error {
	expectedFrom := filemanager.NormalizeFileStatus(params.ExpectedFrom)
	toStatus := filemanager.NormalizeFileStatus(params.To)
	if expectedFrom == "" || toStatus == "" {
		return filemanager.ErrInvalidFileStatusTransition
	}
	if !filemanager.CanTransitionFileStatus(expectedFrom, toStatus) {
		return filemanager.ErrInvalidFileStatusTransition
	}

	now := time.Now().UTC()
	updates := map[string]any{
		"status":     toStatus,
		"updated_by": params.UpdatedBy,
	}
	switch toStatus {
	case filemanager.FileStatusProcessing:
		startedAt := now
		if params.ProcessingStartedAt != nil {
			startedAt = params.ProcessingStartedAt.UTC()
		}
		updates["processing_started_at"] = startedAt
		updates["processed_at"] = nil
		updates["validated_at"] = nil
		updates["failed_at"] = nil
		updates["failure_reason"] = nil
		updates["upload_error"] = ""
	case filemanager.FileStatusValidated:
		processedAt := now
		if params.ProcessedAt != nil {
			processedAt = params.ProcessedAt.UTC()
		}
		validatedAt := processedAt
		if params.ValidatedAt != nil {
			validatedAt = params.ValidatedAt.UTC()
		}
		updates["processed_at"] = processedAt
		updates["validated_at"] = validatedAt
		updates["failed_at"] = nil
		updates["failure_reason"] = nil
		updates["upload_error"] = ""
	case filemanager.FileStatusFailed:
		processedAt := now
		if params.ProcessedAt != nil {
			processedAt = params.ProcessedAt.UTC()
		}
		failedAt := now
		if params.FailedAt != nil {
			failedAt = params.FailedAt.UTC()
		}
		reason := "file processing failed"
		if params.FailureReason != nil {
			clean := strings.TrimSpace(*params.FailureReason)
			if clean != "" {
				reason = clean
			}
		}
		updates["processed_at"] = processedAt
		updates["failed_at"] = failedAt
		updates["validated_at"] = nil
		updates["failure_reason"] = reason
		updates["upload_error"] = reason
	}

	res := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("id = ? AND deleted_at IS NULL AND status = ?", params.ID, expectedFrom).
		Updates(updates)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *FileManagerFileRepository) Finalize(ctx context.Context, params filemanager.FileFinalizeParams) error {
	expectedFrom := filemanager.NormalizeFileStatus(params.ExpectedFrom)
	if expectedFrom == "" {
		expectedFrom = filemanager.FileStatusValidated
	}

	updates := map[string]any{
		"folder_id":       params.FolderID,
		"name":            params.Name,
		"name_normalized": params.NameNormalized,
		"extension":       params.Extension,
		"status":          filemanager.FileStatusReady,
		"name_slot":       "live",
		"upload_error":    "",
		"failure_reason":  nil,
		"updated_by":      params.UpdatedBy,
	}

	res := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("id = ? AND deleted_at IS NULL AND status = ?", params.ID, expectedFrom).
		Updates(updates)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *FileManagerFileRepository) Delete(ctx context.Context, id []byte, deletedBy []byte, purgeAt *time.Time) error {
	now := time.Now().UTC()
	nameSlot := deletedNameSlot(id)
	updates := map[string]any{
		"status":     filemanager.FileStatusTrashed,
		"trashed_at": now,
		"deleted_at": now,
		"deleted_by": deletedBy,
		"updated_by": deletedBy,
		"name_slot":  nameSlot,
	}
	if purgeAt != nil {
		v := purgeAt.UTC()
		updates["purge_at"] = v
	}

	res := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("id = ? AND deleted_at IS NULL AND status = ?", id, filemanager.FileStatusReady).
		Updates(updates)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *FileManagerFileRepository) Restore(ctx context.Context, id []byte, restoredBy []byte) error {
	return r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var row filemanager.File
		if err := tx.Model(&filemanager.File{}).Where("id = ?", id).First(&row).Error; err != nil {
			return err
		}
		if row.DeletedAt == nil {
			return gorm.ErrRecordNotFound
		}

		var total int64
		if err := tx.Model(&filemanager.File{}).
			Where("folder_id = ? AND name_normalized = ? AND name_slot = 'live' AND deleted_at IS NULL", row.FolderID, row.NameNormalized).
			Count(&total).Error; err != nil {
			return err
		}
		if total > 0 {
			return gorm.ErrDuplicatedKey
		}

		updates := map[string]any{
			"status":     filemanager.FileStatusReady,
			"trashed_at": nil,
			"deleted_at": nil,
			"deleted_by": nil,
			"purge_at":   nil,
			"updated_by": restoredBy,
			"name_slot":  "live",
		}
		res := tx.Model(&filemanager.File{}).
			Where("id = ? AND deleted_at IS NOT NULL AND status = ?", id, filemanager.FileStatusTrashed).
			Updates(updates)
		if res.Error != nil {
			return res.Error
		}
		if res.RowsAffected == 0 {
			return gorm.ErrRecordNotFound
		}
		return nil
	})
}

func (r *FileManagerFileRepository) Purge(ctx context.Context, id []byte) error {
	res := r.db.WithContext(ctx).
		Where("id = ?", id).
		Delete(&filemanager.File{})
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *FileManagerFileRepository) GetByID(ctx context.Context, id []byte) (*filemanager.File, error) {
	var row filemanager.File
	err := r.db.WithContext(ctx).
		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 *FileManagerFileRepository) GetByIDAny(ctx context.Context, id []byte) (*filemanager.File, error) {
	var row filemanager.File
	err := r.db.WithContext(ctx).
		Where("id = ?", id).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

func (r *FileManagerFileRepository) GetByObjectKey(ctx context.Context, objectKey string) (*filemanager.File, error) {
	objectKey = strings.TrimSpace(objectKey)
	if objectKey == "" {
		return nil, nil
	}

	var row filemanager.File
	err := r.db.WithContext(ctx).
		Where("object_key = ? AND deleted_at IS NULL", objectKey).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

func (r *FileManagerFileRepository) GetByFolderAndName(ctx context.Context, folderID []byte, nameNormalized string) (*filemanager.File, error) {
	var row filemanager.File
	err := r.db.WithContext(ctx).
		Where("folder_id = ? AND name_normalized = ? AND name_slot = 'live' AND deleted_at IS NULL", folderID, nameNormalized).
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

func (r *FileManagerFileRepository) GetByTemplateAndTakeover(ctx context.Context, templateUUID, takeoverID []byte) (*filemanager.File, error) {
	if len(templateUUID) != 16 || len(takeoverID) != 16 {
		return nil, nil
	}

	var row filemanager.File
	err := r.db.WithContext(ctx).
		Where("template_uuid = ? AND takeover_id = ? AND deleted_at IS NULL", templateUUID, takeoverID).
		Order("updated_at DESC, created_at DESC, id DESC").
		First(&row).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, nil
	}
	return &row, err
}

func (r *FileManagerFileRepository) ListEditedTemplatesByTakeoverID(ctx context.Context, takeoverID []byte) ([]filemanager.File, error) {
	if len(takeoverID) != 16 {
		return nil, nil
	}
	rows := make([]filemanager.File, 0)
	err := r.db.WithContext(ctx).
		Where("takeover_id = ? AND template_uuid IS NOT NULL AND deleted_at IS NULL AND lifecycle_deleted_at IS NULL AND status != ?", takeoverID, filemanager.FileStatusTrashed).
		Order("created_at ASC, id ASC").
		Find(&rows).Error
	return rows, err
}

func (r *FileManagerFileRepository) ListByFolder(ctx context.Context, folderID []byte, sort string, limit, offset int) ([]filemanager.File, int64, error) {
	var rows []filemanager.File
	var total int64

	base := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("folder_id = ? AND deleted_at IS NULL AND name_slot = 'live'", folderID)

	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 *FileManagerFileRepository) SearchByName(ctx context.Context, name string, sort string, limit, offset int) ([]filemanager.File, int64, error) {
	var rows []filemanager.File
	var total int64

	base := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("deleted_at IS NULL AND name_slot = 'live' AND name LIKE ?", "%"+name+"%")

	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 *FileManagerFileRepository) ListTrashByFolder(ctx context.Context, folderID []byte, sort string, limit, offset int) ([]filemanager.File, int64, error) {
	var rows []filemanager.File
	var total int64

	base := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("deleted_at IS NOT NULL")
	if len(folderID) != 0 {
		base = base.Where("folder_id = ?", folderID)
	}

	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 *FileManagerFileRepository) UpdateLifecycle(ctx context.Context, params filemanager.FileLifecycleTransitionParams) error {
	status := filemanager.NormalizeFileLifecycleStatus(params.Status)
	if !filemanager.IsKnownFileLifecycleStatus(status) {
		return fmt.Errorf("invalid file lifecycle status: %s", params.Status)
	}
	updates := map[string]any{
		"lifecycle_status": status,
		"updated_by":       params.UpdatedBy,
	}
	if params.AttachedAt != nil {
		updates["attached_at"] = params.AttachedAt.UTC()
	}
	if params.OrphanedAt != nil {
		updates["orphaned_at"] = params.OrphanedAt.UTC()
	}
	if params.ClearOrphanedAt {
		updates["orphaned_at"] = nil
	}
	if params.LifecycleDelete != nil {
		updates["lifecycle_deleted_at"] = params.LifecycleDelete.UTC()
	}
	res := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("id = ?", params.ID).
		Updates(updates)
	if res.Error != nil {
		return res.Error
	}
	if res.RowsAffected == 0 {
		return gorm.ErrRecordNotFound
	}
	return nil
}

func (r *FileManagerFileRepository) ListFilesByLifecycle(ctx context.Context, filter filemanager.FileLifecycleListFilter) ([]filemanager.File, error) {
	status := filemanager.NormalizeFileLifecycleStatus(filter.Status)
	if !filemanager.IsKnownFileLifecycleStatus(status) {
		return []filemanager.File{}, nil
	}
	query := r.db.WithContext(ctx).
		Model(&filemanager.File{}).
		Where("lifecycle_status = ?", status)
	if filter.OrphanedBeforeEq != nil {
		query = query.Where("orphaned_at IS NOT NULL AND orphaned_at <= ?", filter.OrphanedBeforeEq.UTC())
	}
	if filter.CreatedBeforeEq != nil {
		query = query.Where("created_at <= ?", filter.CreatedBeforeEq.UTC())
	}
	if filter.ExcludeInTrash {
		query = query.Where("deleted_at IS NULL")
	}
	if filter.ExcludeLifecycleDeleted {
		query = query.Where("lifecycle_deleted_at IS NULL")
	}
	if filter.Limit > 0 {
		query = query.Limit(filter.Limit)
	}
	rows := make([]filemanager.File, 0)
	if err := query.Order("orphaned_at ASC, id ASC").Find(&rows).Error; err != nil {
		return nil, err
	}
	return rows, nil
}

func (r *FileManagerFileRepository) IsFileReferenced(ctx context.Context, fileID []byte) (bool, error) {
	if len(fileID) != 16 {
		return false, nil
	}
	var total int64
	queries := []struct {
		sql  string
		args []any
	}{
		{sql: "SELECT COUNT(1) FROM attachments WHERE file_id = ?", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM users u JOIN attachments a ON a.id = u.image_attachment_id WHERE a.file_id = ?", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM bases b JOIN attachments a ON a.id = b.foto_attachment_id WHERE a.file_id = ? AND b.deleted_at IS NULL", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM helicopters h JOIN attachments a ON a.id = h.foto_attachment_id WHERE a.file_id = ?", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM helicopter_files hf JOIN attachments a ON a.id = hf.file_attachment_id WHERE a.file_id = ?", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM hems_operational_files hof JOIN attachments a ON a.id = hof.file_attachment_id JOIN hems_operational_data hod ON hod.id = hof.operational_data_id WHERE a.file_id = ? AND hod.deleted_at IS NULL", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM master_settings ms JOIN file_files f ON f.id = ms.logo_file_id WHERE f.id = ? AND ms.deleted_at IS NULL", args: []any{fileID}},
		{sql: "SELECT COUNT(1) FROM master_settings ms JOIN file_files f ON f.id = ms.cover_file_id WHERE f.id = ? AND ms.deleted_at IS NULL", args: []any{fileID}},
	}
	for i := range queries {
		total = 0
		if err := r.db.WithContext(ctx).Raw(queries[i].sql, queries[i].args...).Scan(&total).Error; err != nil {
			return false, err
		}
		if total > 0 {
			return true, nil
		}
	}

	fileUUID, err := uuidv7.BytesToString(fileID)
	if err == nil && strings.TrimSpace(fileUUID) != "" {
		legacyQueries := []string{
			"SELECT COUNT(1) FROM pilot_profiles WHERE TRIM(photo_file_id) = ?",
			"SELECT COUNT(1) FROM doctor_profiles WHERE TRIM(photo_file_id) = ?",
			"SELECT COUNT(1) FROM air_rescuer_profiles WHERE TRIM(photo_file_id) = ?",
			"SELECT COUNT(1) FROM technician_profiles WHERE TRIM(photo_file_id) = ?",
			"SELECT COUNT(1) FROM flight_assistant_profiles WHERE TRIM(photo_file_id) = ?",
			"SELECT COUNT(1) FROM staff_profiles WHERE TRIM(photo_file_id) = ?",
		}
		for i := range legacyQueries {
			total = 0
			if err := r.db.WithContext(ctx).Raw(legacyQueries[i], fileUUID).Scan(&total).Error; err != nil {
				return false, err
			}
			if total > 0 {
				return true, nil
			}
		}
	}
	return false, nil
}