package repository

import (
	"context"
	"lune/talentscale/internal/domain"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

type postgresDashboardRepository struct {
	db *pgxpool.Pool
}

func NewPostgresDashboardRepository(db *pgxpool.Pool) domain.DashboardRepository {
	return &postgresDashboardRepository{db: db}
}

func (r *postgresDashboardRepository) GetTotalUsers(ctx context.Context, companyID uuid.UUID) (int, error) {
	var count int
	err := r.db.QueryRow(ctx, "SELECT COUNT(*) FROM users WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND deleted_at IS NULL", companyID).Scan(&count)
	return count, err
}

func (r *postgresDashboardRepository) GetTotalCandidates(ctx context.Context, companyID uuid.UUID) (int, error) {
	var count int
	err := r.db.QueryRow(ctx, "SELECT COUNT(*) FROM candidates WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND deleted_at IS NULL", companyID).Scan(&count)
	return count, err
}

func (r *postgresDashboardRepository) GetCandidateStatusBreakdown(ctx context.Context, companyID uuid.UUID) (map[string]int, error) {
	res := make(map[string]int)
	res["new"] = 0
	res["interview"] = 0
	res["hired"] = 0
	res["rejected"] = 0

	query := `SELECT status, COUNT(*) FROM candidates WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND deleted_at IS NULL GROUP BY status`
	rows, err := r.db.Query(ctx, query, companyID)
	if err != nil {
		// If column "status" missing, return defaults instead of crashing
		return res, nil 
	}
	defer rows.Close()

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

func (r *postgresDashboardRepository) GetCandidatesPerDay(ctx context.Context, companyID uuid.UUID, days int) ([]domain.CandidatesPerDayStat, error) {
	query := `
		SELECT TO_CHAR(created_at, 'YYYY-MM-DD') as date, COUNT(*) 
		FROM candidates 
		WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND created_at >= NOW() - INTERVAL '1 day' * $2 AND deleted_at IS NULL
		GROUP BY date 
		ORDER BY date ASC
	`
	rows, err := r.db.Query(ctx, query, companyID, days)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stats []domain.CandidatesPerDayStat
	for rows.Next() {
		var s domain.CandidatesPerDayStat
		if err := rows.Scan(&s.Date, &s.Count); err != nil {
			return nil, err
		}
		stats = append(stats, s)
	}
	return stats, nil
}

func (r *postgresDashboardRepository) GetRecentActivity(ctx context.Context, companyID uuid.UUID, limit int) ([]domain.ActivityLog, error) {
	// For now, let's use a dummy implementation or a simple query from candidates
	// In a real app, we might have an activity_logs table.
	query := `
		SELECT id, name as user, 'applied for position' as action, TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') as timestamp
		FROM candidates
		WHERE ($1 = '00000000-0000-0000-0000-000000000000'::uuid OR company_id = $1) AND deleted_at IS NULL
		ORDER BY created_at DESC
		LIMIT $2
	`
	rows, err := r.db.Query(ctx, query, companyID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var logs []domain.ActivityLog
	for rows.Next() {
		var l domain.ActivityLog
		if err := rows.Scan(&l.ID, &l.User, &l.Action, &l.Timestamp); err != nil {
			return nil, err
		}
		logs = append(logs, l)
	}
	return logs, nil
}