package repository

import (
	"context"
	"lune/talentscale/internal/domain"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

type postgresRecruitmentRepository struct {
	db *pgxpool.Pool
}

func NewPostgresRecruitmentRepository(db *pgxpool.Pool) domain.RecruitmentRepository {
	return &postgresRecruitmentRepository{db: db}
}

func (r *postgresRecruitmentRepository) CreateJob(ctx context.Context, job *domain.Job) error {
	query := `INSERT INTO jobs (id, company_id, title, description, location, type, salary_min, salary_max, is_active, created_at)
			  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`
	_, err := r.db.Exec(ctx, query, job.ID, job.CompanyID, job.Title, job.Description, job.Location, job.Type, job.SalaryMin, job.SalaryMax, job.IsActive, job.CreatedAt)
	return err
}

func (r *postgresRecruitmentRepository) GetJobByID(ctx context.Context, id uuid.UUID) (*domain.Job, error) {
	job := &domain.Job{}
	query := `SELECT id, company_id, title, description, location, type, salary_min, salary_max, is_active, created_at FROM jobs WHERE id = $1`
	err := r.db.QueryRow(ctx, query, id).Scan(&job.ID, &job.CompanyID, &job.Title, &job.Description, &job.Location, &job.Type, &job.SalaryMin, &job.SalaryMax, &job.IsActive, &job.CreatedAt)
	if err != nil {
		return nil, err
	}
	return job, nil
}

func (r *postgresRecruitmentRepository) ListJobs(ctx context.Context, companyID uuid.UUID, activeOnly bool) ([]domain.Job, error) {
	query := `SELECT id, company_id, title, description, location, type, salary_min, salary_max, is_active, created_at FROM jobs WHERE company_id = $1`
	if activeOnly {
		query += " AND is_active = true"
	}
	query += " ORDER BY created_at DESC"

	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var jobs []domain.Job
	for rows.Next() {
		var job domain.Job
		if err := rows.Scan(&job.ID, &job.CompanyID, &job.Title, &job.Description, &job.Location, &job.Type, &job.SalaryMin, &job.SalaryMax, &job.IsActive, &job.CreatedAt); err != nil {
			return nil, err
		}
		jobs = append(jobs, job)
	}
	return jobs, nil
}

func (r *postgresRecruitmentRepository) UpdateJob(ctx context.Context, job *domain.Job) error {
	query := `UPDATE jobs SET title = $1, description = $2, location = $3, type = $4, salary_min = $5, salary_max = $6, is_active = $7 WHERE id = $8`
	_, err := r.db.Exec(ctx, query, job.Title, job.Description, job.Location, job.Type, job.SalaryMin, job.SalaryMax, job.IsActive, job.ID)
	return err
}

func (r *postgresRecruitmentRepository) DeleteJob(ctx context.Context, id uuid.UUID) error {
	_, err := r.db.Exec(ctx, "DELETE FROM jobs WHERE id = $1", id)
	return err
}

func (r *postgresRecruitmentRepository) CreateApplication(ctx context.Context, app *domain.Application) error {
	query := `INSERT INTO applications (id, candidate_id, job_id, company_id, status, source, applied_at, updated_at)
			  VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query, app.ID, app.CandidateID, app.JobID, app.CompanyID, app.Status, app.Source, app.AppliedAt, app.UpdatedAt)
	return err
}

func (r *postgresRecruitmentRepository) GetApplicationByID(ctx context.Context, id uuid.UUID) (*domain.Application, error) {
	app := &domain.Application{}
	query := `SELECT id, candidate_id, job_id, company_id, status, source, applied_at, updated_at FROM applications WHERE id = $1`
	err := r.db.QueryRow(ctx, query, id).Scan(&app.ID, &app.CandidateID, &app.JobID, &app.CompanyID, &app.Status, &app.Source, &app.AppliedAt, &app.UpdatedAt)
	if err != nil {
		return nil, err
	}
	return app, nil
}

func (r *postgresRecruitmentRepository) ListApplications(ctx context.Context, companyID uuid.UUID, jobID *uuid.UUID, status string) ([]domain.Application, error) {
	query := `SELECT a.id, a.candidate_id, a.job_id, a.company_id, a.status, a.source, a.applied_at, a.updated_at,
					 j.title, c.name, c.email
			  FROM applications a
			  JOIN jobs j ON a.job_id = j.id
			  JOIN candidates c ON a.candidate_id = c.id
			  WHERE a.company_id = $1`
	
	args := []interface{}{companyID}
	placeholderIdx := 2

	if jobID != nil {
		query += ` AND a.job_id = $2`
		args = append(args, *jobID)
		placeholderIdx++
	}
	// Simplified logic for pgx compatibility
	if status != "" {
		if jobID != nil {
			query += ` AND a.status = $3`
		} else {
			query += ` AND a.status = $2`
		}
		args = append(args, status)
	}

	query += " ORDER BY a.applied_at DESC"

	rows, err := r.db.Query(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var apps []domain.Application
	for rows.Next() {
		var app domain.Application
		var jobTitle, candName, candEmail string
		err := rows.Scan(&app.ID, &app.CandidateID, &app.JobID, &app.CompanyID, &app.Status, &app.Source, &app.AppliedAt, &app.UpdatedAt,
						 &jobTitle, &candName, &candEmail)
		if err != nil {
			return nil, err
		}
		app.Job = &domain.Job{Title: jobTitle}
		app.Candidate = &domain.Candidate{Name: candName, Email: candEmail}
		apps = append(apps, app)
	}
	return apps, nil
}

func (r *postgresRecruitmentRepository) UpdateApplicationStatus(ctx context.Context, id uuid.UUID, status string) error {
	query := `UPDATE applications SET status = $1, updated_at = CURRENT_TIMESTAMP WHERE id = $2`
	_, err := r.db.Exec(ctx, query, status, id)
	return err
}

func (r *postgresRecruitmentRepository) CreateHistory(ctx context.Context, h *domain.ApplicationHistory) error {
	query := `INSERT INTO application_histories (id, application_id, status, notes, created_by, created_at)
			  VALUES ($1, $2, $3, $4, $5, $6)`
	_, err := r.db.Exec(ctx, query, h.ID, h.ApplicationID, h.Status, h.Notes, h.CreatedBy, h.CreatedAt)
	return err
}

func (r *postgresRecruitmentRepository) GetHistoryByApplication(ctx context.Context, appID uuid.UUID) ([]domain.ApplicationHistory, error) {
	query := `SELECT id, application_id, status, notes, created_by, created_at FROM application_histories WHERE application_id = $1 ORDER BY created_at DESC`
	rows, err := r.db.Query(ctx, query, appID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var histories []domain.ApplicationHistory
	for rows.Next() {
		var h domain.ApplicationHistory
		if err := rows.Scan(&h.ID, &h.ApplicationID, &h.Status, &h.Notes, &h.CreatedBy, &h.CreatedAt); err != nil {
			return nil, err
		}
		histories = append(histories, h)
	}
	return histories, nil
}