package repository

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

type postgresQuestionRepository struct {
	db *pgxpool.Pool
}

func NewPostgresQuestionRepository(db *pgxpool.Pool) domain.QuestionRepository {
	return &postgresQuestionRepository{db: db}
}

func (r *postgresQuestionRepository) Create(ctx context.Context, q *domain.Question) error {
	tx, err := r.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	configJSON, _ := json.Marshal(q.Config)
	answerKeyJSON, _ := json.Marshal(q.AnswerKey)

	query := `INSERT INTO questions (id, test_type_id, question_type, title, instruction, question_text, question_media, config, answer_key, scoring_weight, difficulty, discrimination, order_number, is_active, version, created_at, updated_at)
			  VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)`

	_, err = tx.Exec(ctx, query, q.ID, q.TestTypeID, q.QuestionType, q.Title, q.Instruction, q.QuestionText, q.QuestionMedia, configJSON, answerKeyJSON, q.ScoringWeight, q.Difficulty, q.Discrimination, q.OrderNumber, q.IsActive, q.Version, q.CreatedAt, q.UpdatedAt)
	if err != nil {
		return err
	}

	for _, item := range q.Items {
		metaJSON, _ := json.Marshal(item.Meta)
		itemQuery := `INSERT INTO question_items (id, question_id, item_order, content, media, value, meta, created_at)
					  VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
		_, err = tx.Exec(ctx, itemQuery, item.ID, item.QuestionID, item.ItemOrder, item.Content, item.Media, item.Value, metaJSON, item.CreatedAt)
		if err != nil {
			return err
		}
	}

	return tx.Commit(ctx)
}

func (r *postgresQuestionRepository) GetByID(ctx context.Context, id uuid.UUID) (*domain.Question, error) {
	var q domain.Question
	var configJSON, answerKeyJSON []byte
	query := `SELECT id, test_type_id, question_type, title, instruction, question_text, question_media, config, answer_key, scoring_weight, difficulty, discrimination, order_number, is_active, version, created_at, updated_at FROM questions WHERE id = $1`
	err := r.db.QueryRow(ctx, query, id).Scan(&q.ID, &q.TestTypeID, &q.QuestionType, &q.Title, &q.Instruction, &q.QuestionText, &q.QuestionMedia, &configJSON, &answerKeyJSON, &q.ScoringWeight, &q.Difficulty, &q.Discrimination, &q.OrderNumber, &q.IsActive, &q.Version, &q.CreatedAt, &q.UpdatedAt)
	if err != nil {
		return nil, err
	}
	json.Unmarshal(configJSON, &q.Config)
	json.Unmarshal(answerKeyJSON, &q.AnswerKey)

	// Fetch items
	itemQuery := `SELECT id, question_id, item_order, content, media, value, meta, created_at FROM question_items WHERE question_id = $1 ORDER BY item_order`
	rows, err := r.db.Query(ctx, itemQuery, q.ID)
	if err != nil {
		return &q, nil
	}
	defer rows.Close()

	for rows.Next() {
		var item domain.QuestionItem
		var metaJSON []byte
		if err := rows.Scan(&item.ID, &item.QuestionID, &item.ItemOrder, &item.Content, &item.Media, &item.Value, &metaJSON, &item.CreatedAt); err != nil {
			continue
		}
		json.Unmarshal(metaJSON, &item.Meta)
		q.Items = append(q.Items, &item)
	}

	return &q, nil
}

func (r *postgresQuestionRepository) GetByTestType(ctx context.Context, testTypeID uuid.UUID) ([]*domain.Question, error) {
	query := `SELECT id, test_type_id, question_type, title, instruction, question_text, question_media, config, scoring_weight, difficulty, order_number, is_active, version FROM questions WHERE test_type_id = $1 AND deleted_at IS NULL ORDER BY order_number`
	rows, err := r.db.Query(ctx, query, testTypeID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var qs []*domain.Question
	for rows.Next() {
		var q domain.Question
		var configJSON []byte
		if err := rows.Scan(&q.ID, &q.TestTypeID, &q.QuestionType, &q.Title, &q.Instruction, &q.QuestionText, &q.QuestionMedia, &configJSON, &q.ScoringWeight, &q.Difficulty, &q.OrderNumber, &q.IsActive, &q.Version); err != nil {
			return nil, err
		}
		json.Unmarshal(configJSON, &q.Config)
		qs = append(qs, &q)
	}
	return qs, nil
}

func (r *postgresQuestionRepository) Update(ctx context.Context, q *domain.Question) error {
	tx, err := r.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	configJSON, _ := json.Marshal(q.Config)
	answerKeyJSON, _ := json.Marshal(q.AnswerKey)

	query := `UPDATE questions SET test_type_id=$1, question_type=$2, title=$3, instruction=$4, question_text=$5, question_media=$6, config=$7, answer_key=$8, scoring_weight=$9, difficulty=$10, discrimination=$11, order_number=$12, is_active=$13, version=$14, updated_at=$15 WHERE id=$16`
	_, err = tx.Exec(ctx, query, q.TestTypeID, q.QuestionType, q.Title, q.Instruction, q.QuestionText, q.QuestionMedia, configJSON, answerKeyJSON, q.ScoringWeight, q.Difficulty, q.Discrimination, q.OrderNumber, q.IsActive, q.Version, q.UpdatedAt, q.ID)
	if err != nil {
		return err
	}

	// Create new items (usecase already deleted old ones)
	for _, item := range q.Items {
		metaJSON, _ := json.Marshal(item.Meta)
		itemQuery := `INSERT INTO question_items (id, question_id, item_order, content, media, value, meta, created_at)
					  VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
		_, err = tx.Exec(ctx, itemQuery, item.ID, item.QuestionID, item.ItemOrder, item.Content, item.Media, item.Value, metaJSON, item.CreatedAt)
		if err != nil {
			return err
		}
	}

	return tx.Commit(ctx)
}

func (r *postgresQuestionRepository) Delete(ctx context.Context, id uuid.UUID) error {
	query := `UPDATE questions SET deleted_at = NOW() WHERE id = $1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}

func (r *postgresQuestionRepository) CreateItem(ctx context.Context, item *domain.QuestionItem) error {
	metaJSON, _ := json.Marshal(item.Meta)
	query := `INSERT INTO question_items (id, question_id, item_order, content, media, value, meta, created_at)
			  VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query, item.ID, item.QuestionID, item.ItemOrder, item.Content, item.Media, item.Value, metaJSON, item.CreatedAt)
	return err
}

func (r *postgresQuestionRepository) DeleteItemsByQuestionID(ctx context.Context, questionID uuid.UUID) error {
	query := `DELETE FROM question_items WHERE question_id = $1`
	_, err := r.db.Exec(ctx, query, questionID)
	return err
}