package mysql

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

	"gorm.io/gorm"

	"wucher/internal/domain/federal_state"
	"wucher/internal/shared/pkg/apperrors"
	"wucher/internal/shared/pkg/sortkey"
)

type FederalStateRepository struct {
	db     *gorm.DB
	schema federalStateSchema
}

func NewFederalStateRepository(db *gorm.DB) *FederalStateRepository {
	probe := newSchemaCache(db)
	schema := federalStateSchema{}

	schema.hasFederalStatesTable = probe.HasTable("federal_states")
	schema.federalStatesHasName = schema.hasFederalStatesTable && probe.HasColumn("federal_states", "name")

	schema.hasInsurancePatientDataTable = probe.HasTable("insurance_patient_data")
	if schema.hasInsurancePatientDataTable {
		schema.insurancePatientDataHasFederalStateID = probe.HasColumn("insurance_patient_data", "federal_state_id")
		schema.insurancePatientDataHasState = probe.HasColumn("insurance_patient_data", "state")
		schema.insurancePatientDataHasDeletedAt = probe.HasColumn("insurance_patient_data", "deleted_at")
	}

	schema.hasHealthInsuranceCompaniesTable = probe.HasTable("health_insurance_companies")
	if schema.hasHealthInsuranceCompaniesTable {
		schema.healthInsuranceCompaniesHasFederalStateID = probe.HasColumn("health_insurance_companies", "federal_state_id")
		schema.healthInsuranceCompaniesHasState = probe.HasColumn("health_insurance_companies", "state")
		schema.healthInsuranceCompaniesHasDeletedAt = probe.HasColumn("health_insurance_companies", "deleted_at")
	}

	schema.hasIcaosTable = probe.HasTable("icaos")
	if schema.hasIcaosTable {
		schema.icaosHasFederalStateID = probe.HasColumn("icaos", "federal_state_id")
		schema.icaosHasDeletedAt = probe.HasColumn("icaos", "deleted_at")
	}

	return &FederalStateRepository{db: db, schema: schema}
}

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

func (r *FederalStateRepository) Update(ctx context.Context, row *federal_state.FederalState) error {
	return r.db.WithContext(ctx).Save(row).Error
}

func (r *FederalStateRepository) Delete(ctx context.Context, id []byte, deletedBy []byte) error {
	if err := ensureNoReferenceBeforeDelete(ctx, r.db, "federal_states", id); err != nil {
		return err
	}
	if err := r.ensureNoFederalStateUsage(ctx, 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(&federal_state.FederalState{}).
		Where("id = ? AND deleted_at IS NULL", id).
		Updates(updates).Error)
}

func (r *FederalStateRepository) ensureNoFederalStateUsage(ctx context.Context, id []byte) error {
	if len(id) != 16 {
		return nil
	}
	related := make([]string, 0, 3)
	db := r.db.WithContext(ctx)
	stateName := ""
	if r.schema.hasFederalStatesTable && r.schema.federalStatesHasName {
		_ = db.Table("federal_states").Select("name").Where("id = ?", id).Limit(1).Scan(&stateName).Error
	}
	stateName = strings.TrimSpace(stateName)

	if r.schema.hasInsurancePatientDataTable {
		conditions := make([]string, 0, 2)
		args := make([]any, 0, 2)
		if r.schema.insurancePatientDataHasFederalStateID {
			conditions = append(conditions, "federal_state_id = ?")
			args = append(args, id)
		}
		if stateName != "" && r.schema.insurancePatientDataHasState {
			conditions = append(conditions, "LOWER(TRIM(state)) = LOWER(TRIM(?))")
			args = append(args, stateName)
		}
		if len(conditions) == 0 {
			goto checkHealthInsuranceCompanies
		}
		query := db.Table("insurance_patient_data").Where("("+strings.Join(conditions, " OR ")+")", args...)
		if r.schema.insurancePatientDataHasDeletedAt {
			query = query.Where("deleted_at IS NULL")
		}
		var count int64
		if err := query.Count(&count).Error; err != nil {
			return err
		}
		if count > 0 {
			related = append(related, "insurance patient data")
		}
	}

checkHealthInsuranceCompanies:
	if r.schema.hasHealthInsuranceCompaniesTable {
		conditions := make([]string, 0, 2)
		args := make([]any, 0, 2)
		if r.schema.healthInsuranceCompaniesHasFederalStateID {
			conditions = append(conditions, "federal_state_id = ?")
			args = append(args, id)
		}
		if stateName != "" && r.schema.healthInsuranceCompaniesHasState {
			conditions = append(conditions, "LOWER(TRIM(state)) = LOWER(TRIM(?))")
			args = append(args, stateName)
		}
		if len(conditions) == 0 {
			goto checkICAOs
		}
		query := db.Table("health_insurance_companies").Where("("+strings.Join(conditions, " OR ")+")", args...)
		if r.schema.healthInsuranceCompaniesHasDeletedAt {
			query = query.Where("deleted_at IS NULL")
		}
		var count int64
		if err := query.Count(&count).Error; err != nil {
			return err
		}
		if count > 0 {
			related = append(related, "health insurance companies")
		}
	}

checkICAOs:
	if r.schema.hasIcaosTable {
		if !r.schema.icaosHasFederalStateID {
			goto finalize
		}
		var count int64
		query := db.Table("icaos").Where("federal_state_id = ?", id)
		if r.schema.icaosHasDeletedAt {
			query = query.Where("deleted_at IS NULL")
		}
		if err := query.Count(&count).Error; err != nil {
			return err
		}
		if count > 0 {
			related = append(related, "icaos")
		}
	}

finalize:
	if len(related) > 0 {
		return apperrors.NewDeleteConflictError(related...)
	}
	return nil
}

type federalStateSchema struct {
	hasFederalStatesTable                     bool
	federalStatesHasName                      bool
	hasInsurancePatientDataTable              bool
	insurancePatientDataHasFederalStateID     bool
	insurancePatientDataHasState              bool
	insurancePatientDataHasDeletedAt          bool
	hasHealthInsuranceCompaniesTable          bool
	healthInsuranceCompaniesHasFederalStateID bool
	healthInsuranceCompaniesHasState          bool
	healthInsuranceCompaniesHasDeletedAt      bool
	hasIcaosTable                             bool
	icaosHasFederalStateID                    bool
	icaosHasDeletedAt                         bool
}

func (r *FederalStateRepository) GetByID(ctx context.Context, id []byte) (*federal_state.FederalState, error) {
	var row federal_state.FederalState
	err := r.db.WithContext(ctx).
		Preload("Land", "deleted_at IS NULL").
		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 *FederalStateRepository) List(ctx context.Context, filter, sort string, limit, offset int, landID []byte, landName string) ([]federal_state.FederalState, int64, error) {
	var rows []federal_state.FederalState
	var total int64

	base := r.db.WithContext(ctx).Model(&federal_state.FederalState{}).Where("federal_states.deleted_at IS NULL")
	if filter != "" {
		like := "%" + filter + "%"
		base = base.Where("federal_states.name LIKE ?", like)
	}
	if len(landID) > 0 {
		base = base.Where("federal_states.land_id = ?", landID)
	}
	if trimmedLandName := strings.TrimSpace(landName); trimmedLandName != "" {
		likeLandName := "%" + trimmedLandName + "%"
		base = base.Joins("JOIN lands ON lands.id = federal_states.land_id AND lands.deleted_at IS NULL").
			Where("lands.name LIKE ?", likeLandName)
	}

	query := base
	if sort != "" {
		query = query.Order(sort)
	} else {
		for _, clause := range sortkey.ActivePositiveSortClauses("federal_states", "is_active", "sortkey", "name", 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("Land", "deleted_at IS NULL")
	if err := query.Find(&rows).Error; err != nil {
		return nil, 0, err
	}

	return rows, total, nil
}