package repository

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

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

type postgresAssessmentCategoryRepository struct {
	db *pgxpool.Pool
}

func NewPostgresAssessmentCategoryRepository(db *pgxpool.Pool) domain.AssessmentCategoryRepository {
	return &postgresAssessmentCategoryRepository{db: db}
}

func (r *postgresAssessmentCategoryRepository) CreateCFIT(ctx context.Context, c *domain.CategoryCFIT) error {
	query := `INSERT INTO categories_cfit (id, min_score, max_score, iq, category, description, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query, c.ID, c.MinScore, c.MaxScore, c.IQ, c.Category, c.Description, time.Now(), time.Now())
	return err
}

func (r *postgresAssessmentCategoryRepository) ListCFIT(ctx context.Context) ([]domain.CategoryCFIT, error) {
	query := `SELECT id, min_score, max_score, iq, category, description, created_at, updated_at FROM categories_cfit`
	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []domain.CategoryCFIT
	for rows.Next() {
		var c domain.CategoryCFIT
		if err := rows.Scan(&c.ID, &c.MinScore, &c.MaxScore, &c.IQ, &c.Category, &c.Description, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, c)
	}
	return list, nil
}

func (r *postgresAssessmentCategoryRepository) UpdateCFIT(ctx context.Context, c *domain.CategoryCFIT) error {
	query := `UPDATE categories_cfit SET min_score=$1, max_score=$2, iq=$3, category=$4, description=$5, updated_at=$6 WHERE id=$7`
	_, err := r.db.Exec(ctx, query, c.MinScore, c.MaxScore, c.IQ, c.Category, c.Description, time.Now(), c.ID)
	return err
}

func (r *postgresAssessmentCategoryRepository) DeleteCFIT(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM categories_cfit WHERE id=$1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}

func (r *postgresAssessmentCategoryRepository) CreateDISC(ctx context.Context, c *domain.CategoryDISC) error {
	query := `INSERT INTO categories_disc (id, code, name, color, characteristic, strength, weakness, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`
	_, err := r.db.Exec(ctx, query, c.ID, c.Code, c.Name, c.Color, c.Characteristic, c.Strength, c.Weakness, time.Now(), time.Now())
	return err
}

func (r *postgresAssessmentCategoryRepository) ListDISC(ctx context.Context) ([]domain.CategoryDISC, error) {
	query := `SELECT id, code, name, color, characteristic, strength, weakness, created_at, updated_at FROM categories_disc`
	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []domain.CategoryDISC
	for rows.Next() {
		var c domain.CategoryDISC
		if err := rows.Scan(&c.ID, &c.Code, &c.Name, &c.Color, &c.Characteristic, &c.Strength, &c.Weakness, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, c)
	}
	return list, nil
}

func (r *postgresAssessmentCategoryRepository) UpdateDISC(ctx context.Context, c *domain.CategoryDISC) error {
	query := `UPDATE categories_disc SET code=$1, name=$2, color=$3, characteristic=$4, strength=$5, weakness=$6, updated_at=$7 WHERE id=$8`
	_, err := r.db.Exec(ctx, query, c.Code, c.Name, c.Color, c.Characteristic, c.Strength, c.Weakness, time.Now(), c.ID)
	return err
}

func (r *postgresAssessmentCategoryRepository) DeleteDISC(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM categories_disc WHERE id=$1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}

func (r *postgresAssessmentCategoryRepository) CreateMBTI(ctx context.Context, c *domain.CategoryMBTI) error {
	query := `INSERT INTO categories_mbti (id, code, name, traits, careers, description, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query, c.ID, c.Code, c.Name, c.Traits, c.Careers, c.Description, time.Now(), time.Now())
	return err
}

func (r *postgresAssessmentCategoryRepository) ListMBTI(ctx context.Context) ([]domain.CategoryMBTI, error) {
	query := `SELECT id, code, name, traits, careers, description, created_at, updated_at FROM categories_mbti`
	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []domain.CategoryMBTI
	for rows.Next() {
		var c domain.CategoryMBTI
		if err := rows.Scan(&c.ID, &c.Code, &c.Name, &c.Traits, &c.Careers, &c.Description, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, c)
	}
	return list, nil
}

func (r *postgresAssessmentCategoryRepository) UpdateMBTI(ctx context.Context, c *domain.CategoryMBTI) error {
	query := `UPDATE categories_mbti SET code=$1, name=$2, traits=$3, careers=$4, description=$5, updated_at=$6 WHERE id=$7`
	_, err := r.db.Exec(ctx, query, c.Code, c.Name, c.Traits, c.Careers, c.Description, time.Now(), c.ID)
	return err
}

func (r *postgresAssessmentCategoryRepository) DeleteMBTI(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM categories_mbti WHERE id=$1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}

func (r *postgresAssessmentCategoryRepository) CreateMSDT(ctx context.Context, c *domain.CategoryMSDT) error {
	query := `INSERT INTO categories_msdt (id, code, name, description, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6)`
	_, err := r.db.Exec(ctx, query, c.ID, c.Code, c.Name, c.Description, time.Now(), time.Now())
	return err
}

func (r *postgresAssessmentCategoryRepository) ListMSDT(ctx context.Context) ([]domain.CategoryMSDT, error) {
	query := `SELECT id, code, name, description, created_at, updated_at FROM categories_msdt`
	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []domain.CategoryMSDT
	for rows.Next() {
		var c domain.CategoryMSDT
		if err := rows.Scan(&c.ID, &c.Code, &c.Name, &c.Description, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, c)
	}
	return list, nil
}

func (r *postgresAssessmentCategoryRepository) UpdateMSDT(ctx context.Context, c *domain.CategoryMSDT) error {
	query := `UPDATE categories_msdt SET code=$1, name=$2, description=$3, updated_at=$4 WHERE id=$5`
	_, err := r.db.Exec(ctx, query, c.Code, c.Name, c.Description, time.Now(), c.ID)
	return err
}

func (r *postgresAssessmentCategoryRepository) DeleteMSDT(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM categories_msdt WHERE id=$1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}

func (r *postgresAssessmentCategoryRepository) CreatePAPI(ctx context.Context, c *domain.CategoryPAPI) error {
	query := `INSERT INTO categories_papi (id, code, name, dimension, "order", description, created_at, updated_at)
	          VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`
	_, err := r.db.Exec(ctx, query, c.ID, c.Code, c.Name, c.Dimension, c.Order, c.Description, time.Now(), time.Now())
	return err
}

func (r *postgresAssessmentCategoryRepository) ListPAPI(ctx context.Context) ([]domain.CategoryPAPI, error) {
	query := `SELECT id, code, name, dimension, "order", description, created_at, updated_at FROM categories_papi`
	rows, err := r.db.Query(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var list []domain.CategoryPAPI
	for rows.Next() {
		var c domain.CategoryPAPI
		if err := rows.Scan(&c.ID, &c.Code, &c.Name, &c.Dimension, &c.Order, &c.Description, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		list = append(list, c)
	}
	return list, nil
}

func (r *postgresAssessmentCategoryRepository) UpdatePAPI(ctx context.Context, c *domain.CategoryPAPI) error {
	query := `UPDATE categories_papi SET code=$1, name=$2, dimension=$3, "order"=$4, description=$5, updated_at=$6 WHERE id=$7`
	_, err := r.db.Exec(ctx, query, c.Code, c.Name, c.Dimension, c.Order, c.Description, time.Now(), c.ID)
	return err
}

func (r *postgresAssessmentCategoryRepository) DeletePAPI(ctx context.Context, id uuid.UUID) error {
	query := `DELETE FROM categories_papi WHERE id=$1`
	_, err := r.db.Exec(ctx, query, id)
	return err
}