package services

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

	"github.com/rede5/gohorsejobs/backend/internal/dto"
	"github.com/rede5/gohorsejobs/backend/internal/models"
)

type JobService struct {
	DB *sql.DB
}

func NewJobService(db *sql.DB) *JobService {
	return &JobService{DB: db}
}

func (s *JobService) CreateJob(req dto.CreateJobRequest, createdBy string) (*models.Job, error) {
	fmt.Println("[JOB_SERVICE DEBUG] === CreateJob Started ===")
	fmt.Printf("[JOB_SERVICE DEBUG] CompanyID=%s, CreatedBy=%s, Title=%s, Status=%s\n", req.CompanyID, createdBy, req.Title, req.Status)

	query := `
		INSERT INTO jobs (
			company_id, created_by, title, description, salary_min, salary_max, salary_type, currency,
			employment_type, working_hours, location, region_id, city_id,
			requirements, benefits, visa_support, language_level, status, created_at, updated_at, salary_negotiable
		) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21)
		RETURNING id, created_at, updated_at
	`

	job := &models.Job{
		CompanyID:        req.CompanyID,
		CreatedBy:        createdBy,
		Title:            req.Title,
		Description:      req.Description,
		SalaryMin:        req.SalaryMin,
		SalaryMax:        req.SalaryMax,
		SalaryType:       req.SalaryType,
		Currency:         req.Currency,
		SalaryNegotiable: req.SalaryNegotiable,
		EmploymentType:   req.EmploymentType,
		WorkingHours:     req.WorkingHours,
		Location:         req.Location,
		RegionID:         req.RegionID,
		CityID:           req.CityID,
		Requirements:     req.Requirements,
		Benefits:         req.Benefits,
		VisaSupport:      req.VisaSupport,
		LanguageLevel:    req.LanguageLevel,
		Status:           req.Status,
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	}

	fmt.Println("[JOB_SERVICE DEBUG] Executing INSERT query...")
	fmt.Printf("[JOB_SERVICE DEBUG] Job struct: %+v\n", job)

	err := s.DB.QueryRow(
		query,
		job.CompanyID, job.CreatedBy, job.Title, job.Description, job.SalaryMin, job.SalaryMax, job.SalaryType, job.Currency,
		job.EmploymentType, job.WorkingHours, job.Location, job.RegionID, job.CityID,
		job.Requirements, job.Benefits, job.VisaSupport, job.LanguageLevel, job.Status, job.CreatedAt, job.UpdatedAt, job.SalaryNegotiable,
	).Scan(&job.ID, &job.CreatedAt, &job.UpdatedAt)

	if err != nil {
		fmt.Printf("[JOB_SERVICE ERROR] INSERT query failed: %v\n", err)
		return nil, err
	}

	fmt.Printf("[JOB_SERVICE DEBUG] Job created successfully! ID=%s\n", job.ID)
	return job, nil
}

func (s *JobService) GetJobs(filter dto.JobFilterQuery) ([]models.JobWithCompany, int, error) {
	baseQuery := `
                SELECT
                        j.id, j.company_id, j.title, j.description, j.salary_min, j.salary_max, j.salary_type,
                        j.employment_type, j.work_mode, j.working_hours, j.location, j.status, j.salary_negotiable, j.is_featured, j.created_at, j.updated_at,
                        COALESCE(c.name, '') as company_name, c.logo_url as company_logo_url,
                        r.name as region_name, ci.name as city_name,
                        (SELECT COUNT(*) FROM applications a WHERE a.job_id = j.id) as applications_count
                FROM jobs j
                LEFT JOIN companies c ON j.company_id::text = c.id::text
                LEFT JOIN states r ON j.region_id::text = r.id::text
                LEFT JOIN cities ci ON j.city_id::text = ci.id::text
                WHERE 1=1`
	countQuery := `SELECT COUNT(*) FROM jobs j WHERE 1=1`

	var args []interface{}
	argId := 1

	// --- Filters ---
	if filter.CompanyID != nil {
		baseQuery += fmt.Sprintf(" AND j.company_id = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.company_id = $%d", argId)
		args = append(args, *filter.CompanyID)
		argId++
	}

	if filter.IsFeatured != nil {
		baseQuery += fmt.Sprintf(" AND j.is_featured = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.is_featured = $%d", argId)
		args = append(args, *filter.IsFeatured)
		argId++
	}

	if filter.Status != nil && *filter.Status != "" {
		baseQuery += fmt.Sprintf(" AND j.status = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.status = $%d", argId)
		args = append(args, *filter.Status)
		argId++
	}

	if filter.Search != nil && *filter.Search != "" {
		searchTerm := fmt.Sprintf("%%%s%%", *filter.Search)
		baseQuery += fmt.Sprintf(" AND (j.title ILIKE $%d OR j.description ILIKE $%d OR c.name ILIKE $%d)", argId, argId, argId)
		countQuery += fmt.Sprintf(" AND (j.title ILIKE $%d OR j.description ILIKE $%d OR c.name ILIKE $%d)", argId, argId, argId)
		args = append(args, searchTerm)
		argId++
	}

	if filter.Location != nil && *filter.Location != "" && *filter.Location != "all" {
		locTerm := fmt.Sprintf("%%%s%%", *filter.Location)
		baseQuery += fmt.Sprintf(" AND j.location ILIKE $%d", argId)
		countQuery += fmt.Sprintf(" AND j.location ILIKE $%d", argId)
		args = append(args, locTerm)
		argId++
	}

	if filter.EmploymentType != nil && *filter.EmploymentType != "" && *filter.EmploymentType != "all" {
		baseQuery += fmt.Sprintf(" AND j.employment_type = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.employment_type = $%d", argId)
		args = append(args, *filter.EmploymentType)
		argId++
	}

	if filter.WorkMode != nil && *filter.WorkMode != "" && *filter.WorkMode != "all" {
		baseQuery += fmt.Sprintf(" AND j.work_mode = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.work_mode = $%d", argId)
		args = append(args, *filter.WorkMode)
		argId++
	}

	// --- Advanced Filters ---
	if filter.VisaSupport != nil {
		baseQuery += fmt.Sprintf(" AND j.visa_support = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.visa_support = $%d", argId)
		args = append(args, *filter.VisaSupport)
		argId++
	}

	if filter.SalaryMin != nil {
		baseQuery += fmt.Sprintf(" AND j.salary_min >= $%d", argId)
		countQuery += fmt.Sprintf(" AND j.salary_min >= $%d", argId)
		args = append(args, *filter.SalaryMin)
		argId++
	}

	if filter.SalaryMax != nil {
		baseQuery += fmt.Sprintf(" AND j.salary_max <= $%d", argId)
		countQuery += fmt.Sprintf(" AND j.salary_max <= $%d", argId)
		args = append(args, *filter.SalaryMax)
		argId++
	}

	if filter.Currency != nil && *filter.Currency != "" && *filter.Currency != "all" {
		baseQuery += fmt.Sprintf(" AND j.currency = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.currency = $%d", argId)
		args = append(args, *filter.Currency)
		argId++
	}

	if filter.LanguageLevel != nil && *filter.LanguageLevel != "" && *filter.LanguageLevel != "all" {
		baseQuery += fmt.Sprintf(" AND j.language_level = $%d", argId)
		countQuery += fmt.Sprintf(" AND j.language_level = $%d", argId)
		args = append(args, *filter.LanguageLevel)
		argId++
	}

	// --- Sorting ---
	sortClause := " ORDER BY j.is_featured DESC, j.created_at DESC" // default
	if filter.SortBy != nil {
		switch *filter.SortBy {
		case "recent":
			sortClause = " ORDER BY j.created_at DESC"
		case "salary_asc":
			sortClause = " ORDER BY j.salary_min ASC NULLS LAST"
		case "salary_desc":
			sortClause = " ORDER BY j.salary_max DESC NULLS LAST"
		case "relevance":
			sortClause = " ORDER BY j.is_featured DESC, j.created_at DESC"
		}
	}
	baseQuery += sortClause

	// Pagination
	limit := filter.Limit
	if limit == 0 {
		limit = 10
	}
	offset := (filter.Page - 1) * limit
	if offset < 0 {
		offset = 0
	}

	paginationQuery := baseQuery + fmt.Sprintf(" LIMIT $%d OFFSET $%d", argId, argId+1)
	paginationArgs := append(args, limit, offset)

	rows, err := s.DB.Query(paginationQuery, paginationArgs...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	jobs := []models.JobWithCompany{}
	for rows.Next() {
		var j models.JobWithCompany
		if err := rows.Scan(
			&j.ID, &j.CompanyID, &j.Title, &j.Description, &j.SalaryMin, &j.SalaryMax, &j.SalaryType,
			&j.EmploymentType, &j.WorkMode, &j.WorkingHours, &j.Location, &j.Status, &j.SalaryNegotiable, &j.IsFeatured, &j.CreatedAt, &j.UpdatedAt,
			&j.CompanyName, &j.CompanyLogoURL, &j.RegionName, &j.CityName, &j.ApplicationsCount,
		); err != nil {
			return nil, 0, err
		}
		jobs = append(jobs, j)
	}

	var total int
	err = s.DB.QueryRow(countQuery, args...).Scan(&total)
	if err != nil {
		return nil, 0, err
	}

	return jobs, total, nil
}

func (s *JobService) GetJobByID(id string) (*models.Job, error) {
	var j models.Job
	query := `
		SELECT id, company_id, title, description, salary_min, salary_max, salary_type,
		employment_type, working_hours, location, region_id, city_id, salary_negotiable,
		requirements, benefits, visa_support, language_level, status, created_at, updated_at
		FROM jobs WHERE id = $1
	`
	err := s.DB.QueryRow(query, id).Scan(
		&j.ID, &j.CompanyID, &j.Title, &j.Description, &j.SalaryMin, &j.SalaryMax, &j.SalaryType,
		&j.EmploymentType, &j.WorkingHours, &j.Location, &j.RegionID, &j.CityID, &j.SalaryNegotiable,
		&j.Requirements, &j.Benefits, &j.VisaSupport, &j.LanguageLevel, &j.Status, &j.CreatedAt, &j.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &j, nil
}

func (s *JobService) UpdateJob(id string, req dto.UpdateJobRequest) (*models.Job, error) {
	var setClauses []string
	var args []interface{}
	argId := 1

	if req.Title != nil {
		setClauses = append(setClauses, fmt.Sprintf("title = $%d", argId))
		args = append(args, *req.Title)
		argId++
	}
	if req.Description != nil {
		setClauses = append(setClauses, fmt.Sprintf("description = $%d", argId))
		args = append(args, *req.Description)
		argId++
	}
	// Add other fields...
	if req.Status != nil {
		setClauses = append(setClauses, fmt.Sprintf("status = $%d", argId))
		args = append(args, *req.Status)
		argId++
	}
	if req.SalaryNegotiable != nil {
		setClauses = append(setClauses, fmt.Sprintf("salary_negotiable = $%d", argId))
		args = append(args, *req.SalaryNegotiable)
		argId++
	}

	if len(setClauses) == 0 {
		return s.GetJobByID(id)
	}

	setClauses = append(setClauses, "updated_at = NOW()")

	query := fmt.Sprintf("UPDATE jobs SET %s WHERE id = $%d RETURNING id, updated_at", strings.Join(setClauses, ", "), argId)
	args = append(args, id)

	var j models.Job
	err := s.DB.QueryRow(query, args...).Scan(&j.ID, &j.UpdatedAt)
	if err != nil {
		return nil, err
	}

	return s.GetJobByID(id)
}

func (s *JobService) DeleteJob(id string) error {
	_, err := s.DB.Exec("DELETE FROM jobs WHERE id = $1", id)
	return err
}
