package db

import (
	"database/sql"
	"fmt"
	"time"

	"git.leaktechnologies.dev/stu/Goondex/internal/model"
)

// PerformerStore handles CRUD operations for performers
type PerformerStore struct {
	db *DB
}

// NewPerformerStore creates a new performer store
func NewPerformerStore(db *DB) *PerformerStore {
	return &PerformerStore{db: db}
}

// Create inserts a new performer
func (s *PerformerStore) Create(p *model.Performer) error {
	now := time.Now()
	p.CreatedAt = now
	p.UpdatedAt = now

	activeInt := 0
	if p.Active {
		activeInt = 1
	}

	result, err := s.db.conn.Exec(`
		INSERT INTO performers (
			name, aliases,
			gender, birthday, astrology, birthplace, ethnicity, nationality, country,
			eye_color, hair_color, height, weight, measurements, cup_size,
			tattoo_description, piercing_description, boob_job,
			career, career_start_year, career_end_year, date_of_death, active,
			image_path, image_url, poster_url, bio,
			source, source_id, source_numeric_id,
			created_at, updated_at
		) VALUES (
			?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		)
		ON CONFLICT(source, source_id) DO UPDATE SET
			name = excluded.name,
			aliases = excluded.aliases,
			gender = excluded.gender,
			birthday = excluded.birthday,
			astrology = excluded.astrology,
			birthplace = excluded.birthplace,
			ethnicity = excluded.ethnicity,
			nationality = excluded.nationality,
			country = excluded.country,
			eye_color = excluded.eye_color,
			hair_color = excluded.hair_color,
			height = excluded.height,
			weight = excluded.weight,
			measurements = excluded.measurements,
			cup_size = excluded.cup_size,
			tattoo_description = excluded.tattoo_description,
			piercing_description = excluded.piercing_description,
			boob_job = excluded.boob_job,
			career = excluded.career,
			career_start_year = excluded.career_start_year,
			career_end_year = excluded.career_end_year,
			date_of_death = excluded.date_of_death,
			active = excluded.active,
			image_path = excluded.image_path,
			image_url = excluded.image_url,
			poster_url = excluded.poster_url,
			bio = excluded.bio,
			source_numeric_id = excluded.source_numeric_id,
			updated_at = excluded.updated_at
	`,
		p.Name, p.Aliases,
		p.Gender, p.Birthday, p.Astrology, p.Birthplace, p.Ethnicity, p.Nationality, p.Country,
		p.EyeColor, p.HairColor, p.Height, p.Weight, p.Measurements, p.CupSize,
		p.TattooDescription, p.PiercingDescription, p.BoobJob,
		p.Career, p.CareerStartYear, p.CareerEndYear, p.DateOfDeath, activeInt,
		p.ImagePath, p.ImageURL, p.PosterURL, p.Bio,
		p.Source, p.SourceID, p.SourceNumericID,
		p.CreatedAt.Format(time.RFC3339), p.UpdatedAt.Format(time.RFC3339),
	)

	if err != nil {
		return fmt.Errorf("failed to create performer: %w", err)
	}

	id, err := result.LastInsertId()
	if err != nil {
		return fmt.Errorf("failed to get last insert id: %w", err)
	}

	p.ID = id
	return nil
}

// GetByID retrieves a performer by ID
func (s *PerformerStore) GetByID(id int64) (*model.Performer, error) {
	p := &model.Performer{}
	var createdAt, updatedAt string
	var activeInt int

	err := s.db.conn.QueryRow(`
		SELECT
			id, name, COALESCE(aliases, ''),
			COALESCE(gender, ''), COALESCE(birthday, ''), COALESCE(astrology, ''), COALESCE(birthplace, ''), COALESCE(ethnicity, ''), COALESCE(nationality, ''), COALESCE(country, ''),
			COALESCE(eye_color, ''), COALESCE(hair_color, ''), COALESCE(height, 0), COALESCE(weight, 0), COALESCE(measurements, ''), COALESCE(cup_size, ''),
			COALESCE(tattoo_description, ''), COALESCE(piercing_description, ''), COALESCE(boob_job, ''),
			COALESCE(career, ''), COALESCE(career_start_year, 0), COALESCE(career_end_year, 0), COALESCE(date_of_death, ''), COALESCE(active, 0),
			COALESCE(image_path, ''), COALESCE(image_url, ''), COALESCE(poster_url, ''), COALESCE(bio, ''),
			COALESCE(source, ''), COALESCE(source_id, ''), COALESCE(source_numeric_id, 0),
			created_at, updated_at
		FROM performers WHERE id = ?
	`, id).Scan(
		&p.ID, &p.Name, &p.Aliases,
		&p.Gender, &p.Birthday, &p.Astrology, &p.Birthplace, &p.Ethnicity, &p.Nationality, &p.Country,
		&p.EyeColor, &p.HairColor, &p.Height, &p.Weight, &p.Measurements, &p.CupSize,
		&p.TattooDescription, &p.PiercingDescription, &p.BoobJob,
		&p.Career, &p.CareerStartYear, &p.CareerEndYear, &p.DateOfDeath, &activeInt,
		&p.ImagePath, &p.ImageURL, &p.PosterURL, &p.Bio,
		&p.Source, &p.SourceID, &p.SourceNumericID,
		&createdAt, &updatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, fmt.Errorf("performer not found")
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get performer: %w", err)
	}

	p.Active = (activeInt == 1)
	p.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
	p.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

	return p, nil
}

// Search searches for performers by name, ordered by popularity (scene count)
func (s *PerformerStore) Search(query string) ([]model.Performer, error) {
	rows, err := s.db.conn.Query(`
		SELECT
			p.id, p.name, COALESCE(p.aliases, ''),
			COALESCE(p.gender, ''), COALESCE(p.birthday, ''), COALESCE(p.astrology, ''), COALESCE(p.birthplace, ''), COALESCE(p.ethnicity, ''), COALESCE(p.nationality, ''), COALESCE(p.country, ''),
			COALESCE(p.eye_color, ''), COALESCE(p.hair_color, ''), COALESCE(p.height, 0), COALESCE(p.weight, 0), COALESCE(p.measurements, ''), COALESCE(p.cup_size, ''),
			COALESCE(p.tattoo_description, ''), COALESCE(p.piercing_description, ''), COALESCE(p.boob_job, ''),
			COALESCE(p.career, ''), COALESCE(p.career_start_year, 0), COALESCE(p.career_end_year, 0), COALESCE(p.date_of_death, ''), COALESCE(p.active, 0),
			COALESCE(p.image_path, ''), COALESCE(p.image_url, ''), COALESCE(p.poster_url, ''), COALESCE(p.bio, ''),
			COALESCE(p.source, ''), COALESCE(p.source_id, ''), COALESCE(p.source_numeric_id, 0),
			p.created_at, p.updated_at
		FROM performers p
		LEFT JOIN scene_performers sp ON p.id = sp.performer_id
		WHERE p.name LIKE ? OR COALESCE(p.aliases, '') LIKE ?
		GROUP BY p.id
		ORDER BY COUNT(sp.scene_id) DESC, p.name ASC
	`, "%"+query+"%", "%"+query+"%")

	if err != nil {
		return nil, fmt.Errorf("failed to search performers: %w", err)
	}
	defer rows.Close()

	var performers []model.Performer
	for rows.Next() {
		var p model.Performer
		var createdAt, updatedAt string
		var activeInt int

		err := rows.Scan(
			&p.ID, &p.Name, &p.Aliases,
			&p.Gender, &p.Birthday, &p.Astrology, &p.Birthplace, &p.Ethnicity, &p.Nationality, &p.Country,
			&p.EyeColor, &p.HairColor, &p.Height, &p.Weight, &p.Measurements, &p.CupSize,
			&p.TattooDescription, &p.PiercingDescription, &p.BoobJob,
			&p.Career, &p.CareerStartYear, &p.CareerEndYear, &p.DateOfDeath, &activeInt,
			&p.ImagePath, &p.ImageURL, &p.PosterURL, &p.Bio,
			&p.Source, &p.SourceID, &p.SourceNumericID,
			&createdAt, &updatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan performer: %w", err)
		}

		p.Active = (activeInt == 1)
		p.CreatedAt, _ = time.Parse(time.RFC3339, createdAt)
		p.UpdatedAt, _ = time.Parse(time.RFC3339, updatedAt)

		performers = append(performers, p)
	}

	return performers, nil
}

// GetSceneCount returns the number of scenes associated with a performer
func (s *PerformerStore) GetSceneCount(performerID int64) (int, error) {
	var count int
	err := s.db.conn.QueryRow(`
		SELECT COUNT(*) FROM scene_performers WHERE performer_id = ?
	`, performerID).Scan(&count)

	if err != nil {
		return 0, fmt.Errorf("failed to count scenes: %w", err)
	}

	return count, nil
}

// Update updates an existing performer
func (s *PerformerStore) Update(p *model.Performer) error {
	p.UpdatedAt = time.Now()

	result, err := s.db.conn.Exec(`
		UPDATE performers
		SET name = ?, aliases = ?, nationality = ?, country = ?, gender = ?, image_path = ?, image_url = ?, bio = ?, source = ?, source_id = ?, updated_at = ?
		WHERE id = ?
	`, p.Name, p.Aliases, p.Nationality, p.Country, p.Gender, p.ImagePath, p.ImageURL, p.Bio, p.Source, p.SourceID, p.UpdatedAt.Format(time.RFC3339), p.ID)

	if err != nil {
		return fmt.Errorf("failed to update performer: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rows == 0 {
		return fmt.Errorf("performer not found")
	}

	return nil
}

// Delete deletes a performer by ID
func (s *PerformerStore) Delete(id int64) error {
	result, err := s.db.conn.Exec("DELETE FROM performers WHERE id = ?", id)
	if err != nil {
		return fmt.Errorf("failed to delete performer: %w", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rows == 0 {
		return fmt.Errorf("performer not found")
	}

	return nil
}

// Upsert inserts or updates a performer based on source_id
func (s *PerformerStore) Upsert(p *model.Performer) error {
	// Try to find existing performer by source_id
	existing, err := s.GetBySourceID(p.Source, p.SourceID)
	if err == nil && existing != nil {
		// Update existing
		p.ID = existing.ID
		return s.Update(p)
	}
	// Create new
	return s.Create(p)
}

// GetBySourceID retrieves a performer by its source and source_id
func (s *PerformerStore) GetBySourceID(source, sourceID string) (*model.Performer, error) {
	var p model.Performer
	var activeInt int

	err := s.db.conn.QueryRow(`
		SELECT id, name, COALESCE(aliases, ''),
			COALESCE(gender, ''), COALESCE(birthday, ''), COALESCE(astrology, ''),
			COALESCE(birthplace, ''), COALESCE(ethnicity, ''), COALESCE(nationality, ''), COALESCE(country, ''),
			COALESCE(eye_color, ''), COALESCE(hair_color, ''), COALESCE(height, 0), COALESCE(weight, 0),
			COALESCE(measurements, ''), COALESCE(cup_size, ''), COALESCE(tattoo_description, ''), COALESCE(piercing_description, ''), COALESCE(boob_job, ''),
			COALESCE(career, ''), COALESCE(career_start_year, 0), COALESCE(career_end_year, 0), COALESCE(date_of_death, ''), COALESCE(active, 0),
			COALESCE(image_path, ''), COALESCE(image_url, ''), COALESCE(poster_url, ''), COALESCE(bio, ''),
			COALESCE(source, ''), COALESCE(source_id, ''), COALESCE(source_numeric_id, 0),
			created_at, updated_at
		FROM performers
		WHERE source = ? AND source_id = ?
	`, source, sourceID).Scan(
		&p.ID, &p.Name, &p.Aliases,
		&p.Gender, &p.Birthday, &p.Astrology,
		&p.Birthplace, &p.Ethnicity, &p.Nationality, &p.Country,
		&p.EyeColor, &p.HairColor, &p.Height, &p.Weight,
		&p.Measurements, &p.CupSize, &p.TattooDescription, &p.PiercingDescription, &p.BoobJob,
		&p.Career, &p.CareerStartYear, &p.CareerEndYear, &p.DateOfDeath, &activeInt,
		&p.ImagePath, &p.ImageURL, &p.PosterURL, &p.Bio,
		&p.Source, &p.SourceID, &p.SourceNumericID,
		&p.CreatedAt, &p.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get performer: %w", err)
	}

	p.Active = activeInt == 1

	return &p, nil
}