package repository

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

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

type postgresTestSessionRepository struct {
	db *pgxpool.Pool
}

func NewPostgresTestSessionRepository(db *pgxpool.Pool) domain.TestSessionRepository {
	return &postgresTestSessionRepository{db: db}
}

func (r *postgresTestSessionRepository) Create(ctx context.Context, session *domain.TestSession, items []domain.TestSessionItem) error {
	tx, err := r.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	// 1. Create session
	querySession := `
		INSERT INTO test_sessions (
			id, company_id, created_by, title, description, starts_at, ends_at, status, total_candidates, created_at, updated_at
		) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)`
	
	_, err = tx.Exec(ctx, querySession,
		session.ID, session.CompanyID, session.CreatedBy, session.Title, session.Description,
		session.StartsAt, session.EndsAt, session.Status, session.TotalCandidates,
		session.CreatedAt, session.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("failed to create test session: %w", err)
	}

	// 2. Batch Create Items (Manual batching if no helper available)
	if len(items) > 0 {
		batch := &pgx.Batch{}
		for _, item := range items {
			queryItem := `
				INSERT INTO test_session_items (
					id, test_session_id, candidate_id, invitation_email, invited_at, expired_at, created_at
				) VALUES ($1, $2, $3, $4, $5, $6, $7)`
			batch.Queue(queryItem,
				item.ID, item.TestSessionID, item.CandidateID, item.Email,
				item.InvitedAt, item.ExpiredAt, item.CreatedAt,
			)
		}
		
		br := tx.SendBatch(ctx, batch)
		if err := br.Close(); err != nil {
			return fmt.Errorf("failed to batch create test session items: %w", err)
		}
	}

	return tx.Commit(ctx)
}

func (r *postgresTestSessionRepository) GetByID(ctx context.Context, id uuid.UUID, companyID uuid.UUID) (*domain.TestSession, []domain.TestSessionItem, error) {
	querySession := `
		SELECT id, company_id, created_by, title, description, starts_at, ends_at, status, total_candidates, created_at, updated_at
		FROM test_sessions
		WHERE id = $1 AND company_id = $2`
	
	session := &domain.TestSession{}
	err := r.db.QueryRow(ctx, querySession, id, companyID).Scan(
		&session.ID, &session.CompanyID, &session.CreatedBy, &session.Title, &session.Description,
		&session.StartsAt, &session.EndsAt, &session.Status, &session.TotalCandidates,
		&session.CreatedAt, &session.UpdatedAt,
	)
	if err != nil {
		return nil, nil, err
	}

	queryItems := `
		SELECT 
			tsi.id, tsi.test_session_id, tsi.candidate_id, tsi.invitation_email, tsi.invited_at, tsi.expired_at, tsi.created_at,
			COALESCE(el.status, 'pending') as delivery_status
		FROM test_session_items tsi
		LEFT JOIN LATERAL (
			SELECT status FROM email_logs 
			WHERE recipient = tsi.invitation_email 
			ORDER BY created_at DESC LIMIT 1
		) el ON true
		WHERE tsi.test_session_id = $1`
	
	rows, err := r.db.Query(ctx, queryItems, id)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()

	var items []domain.TestSessionItem
	for rows.Next() {
		var item domain.TestSessionItem
		if err := rows.Scan(
			&item.ID, &item.TestSessionID, &item.CandidateID, &item.Email,
			&item.InvitedAt, &item.ExpiredAt, &item.CreatedAt, &item.DeliveryStatus,
		); err != nil {
			return nil, nil, err
		}
		items = append(items, item)
	}

	return session, items, nil
}

func (r *postgresTestSessionRepository) List(ctx context.Context, companyID uuid.UUID, limit, offset int) ([]domain.TestSession, int, error) {
	query := `
		SELECT id, company_id, created_by, title, description, starts_at, ends_at, status, total_candidates, created_at, updated_at, count(*) OVER()
		FROM test_sessions
		WHERE company_id = $1
		ORDER BY created_at DESC
		LIMIT $2 OFFSET $3`
	
	rows, err := r.db.Query(ctx, query, companyID, limit, offset)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()

	var sessions []domain.TestSession
	var total int
	for rows.Next() {
		var s domain.TestSession
		if err := rows.Scan(
			&s.ID, &s.CompanyID, &s.CreatedBy, &s.Title, &s.Description,
			&s.StartsAt, &s.EndsAt, &s.Status, &s.TotalCandidates,
			&s.CreatedAt, &s.UpdatedAt, &total,
		); err != nil {
			return nil, 0, err
		}
		sessions = append(sessions, s)
	}

	return sessions, total, nil
}

func (r *postgresTestSessionRepository) UpdateStatus(ctx context.Context, id uuid.UUID, status domain.TestSessionStatus) error {
	query := `UPDATE test_sessions SET status = $1, updated_at = NOW() WHERE id = $2`
	_, err := r.db.Exec(ctx, query, status, id)
	return err
}