package hr_analytics

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

type Repository interface {
	GetCompletionTrend(ctx context.Context, companyID uuid.UUID) ([]TrendPoint, error)
	GetHiringFunnel(ctx context.Context, companyID uuid.UUID) (*HiringFunnel, error)
	GetPersonalityDistribution(ctx context.Context, companyID uuid.UUID) (map[string]int, error)
	GetMonthlyActivity(ctx context.Context, companyID uuid.UUID) ([]ActivityPoint, error)
}

type postgresRepository struct {
	db *pgxpool.Pool
}

func NewRepository(db *pgxpool.Pool) Repository {
	return &postgresRepository{db: db}
}

func (r *postgresRepository) GetCompletionTrend(ctx context.Context, companyID uuid.UUID) ([]TrendPoint, error) {
	res := []TrendPoint{}
	query := `
		SELECT TO_CHAR(pts.started_at, 'YYYY-MM-DD') AS date, COUNT(*) AS value
		FROM psikotest.test_sessions pts
		JOIN candidates c ON pts.candidate_id = c.id
		WHERE c.company_id = $1
		  AND pts.started_at >= NOW() - INTERVAL '30 days'
		GROUP BY date ORDER BY date ASC
	`
	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		return res, err
	}
	defer rows.Close()

	for rows.Next() {
		var p TrendPoint
		if err := rows.Scan(&p.Date, &p.Value); err == nil {
			res = append(res, p)
		}
	}
	return res, nil
}

func (r *postgresRepository) GetHiringFunnel(ctx context.Context, companyID uuid.UUID) (*HiringFunnel, error) {
	var f HiringFunnel
	
	// Total Candidates
	r.db.QueryRow(ctx, "SELECT COUNT(*) FROM candidates WHERE company_id = $1 AND deleted_at IS NULL", companyID).Scan(&f.TotalCandidates)
	
	// Invited to Test
	r.db.QueryRow(ctx, `
		SELECT COUNT(DISTINCT tsi.candidate_id) 
		FROM test_session_items tsi
		JOIN test_sessions ts ON ts.id = tsi.test_session_id
		WHERE ts.company_id = $1
	`, companyID).Scan(&f.InvitedToTest)
	
	// Completed Test
	r.db.QueryRow(ctx, `
		SELECT COUNT(DISTINCT pts.candidate_id) 
		FROM psikotest.test_sessions pts
		JOIN candidates c ON pts.candidate_id = c.id
		WHERE c.company_id = $1 AND pts.status = 'finished'
	`, companyID).Scan(&f.CompletedTest)
	
	// Passed Screening
	r.db.QueryRow(ctx, "SELECT COUNT(*) FROM candidates WHERE company_id = $1 AND status = 'passed' AND deleted_at IS NULL", companyID).Scan(&f.PassedScreening)
	
	// Interviewed
	r.db.QueryRow(ctx, "SELECT COUNT(*) FROM candidates WHERE company_id = $1 AND status = 'interview' AND deleted_at IS NULL", companyID).Scan(&f.Interviewed)
	
	return &f, nil
}

func (r *postgresRepository) GetPersonalityDistribution(ctx context.Context, companyID uuid.UUID) (map[string]int, error) {
	res := make(map[string]int)
	query := `
		SELECT 
			COALESCE(cr.result->>'type', cr.result->'profile'->>'type', 'Unknown') AS personality_type,
			COUNT(*) AS count
		FROM candidate_results cr
		JOIN candidates c ON cr.candidate_id = c.id
		WHERE c.company_id = $1
		GROUP BY personality_type ORDER BY count DESC
	`
	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		return res, err
	}
	defer rows.Close()

	for rows.Next() {
		var ptype string
		var count int
		if err := rows.Scan(&ptype, &count); err == nil {
			res[ptype] = count
		}
	}
	return res, nil
}

func (r *postgresRepository) GetMonthlyActivity(ctx context.Context, companyID uuid.UUID) ([]ActivityPoint, error) {
	res := []ActivityPoint{}
	query := `
		SELECT TO_CHAR(pts.started_at, 'Mon') AS label,
		       EXTRACT(MONTH FROM pts.started_at) AS month_num,
		       COUNT(*) AS value
		FROM psikotest.test_sessions pts
		JOIN candidates c ON pts.candidate_id = c.id
		WHERE c.company_id = $1
		  AND pts.started_at >= NOW() - INTERVAL '12 months'
		GROUP BY label, month_num ORDER BY month_num ASC
	`
	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		return res, err
	}
	defer rows.Close()

	for rows.Next() {
		var p ActivityPoint
		var monthNum float64
		if err := rows.Scan(&p.Label, &monthNum, &p.Value); err == nil {
			res = append(res, p)
		}
	}
	return res, nil
}