package repositories

import (
	"context"
	"database/sql"

	"github.com/dmitriygnatenko/internal/interfaces"
	"github.com/dmitriygnatenko/internal/models"
)

type tagRepository struct {
	db *sql.DB
}

func InitTagRepository(db *sql.DB) interfaces.ITagRepository {
	return tagRepository{db: db}
}

func (t tagRepository) GetAllUsed(ctx context.Context) ([]models.Tag, error) {
	var res []models.Tag

	query := "SELECT t.id, t.url, t.tag " +
		"FROM " + articleTagTableName + " at, " + tagTableName + " t " +
		"WHERE t.id = at.tag_id AND at.article_id IN" +
		"(SELECT id FROM " + articleTableName + " " + "WHERE is_active = 1) " +
		"GROUP BY t.id"

	rows, err := t.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.Tag{}

		err = rows.Scan(
			&row.ID,
			&row.URL,
			&row.Tag,
		)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	err = rows.Err()
	if err != nil {
		return nil, err
	}

	return res, nil
}

func (t tagRepository) GetByURL(ctx context.Context, tag string) (*models.Tag, error) {
	var res models.Tag

	query := "SELECT id, url, tag " +
		"FROM " + tagTableName + " " +
		"WHERE url = ? " +
		"LIMIT 1"

	err := t.db.QueryRowContext(ctx, query, tag).
		Scan(&res.ID, &res.URL, &res.Tag)

	if err != nil {
		return nil, err
	}

	return &res, nil
}

func (t tagRepository) GetByID(ctx context.Context, ID int) (*models.Tag, error) {
	var res models.Tag

	query := "SELECT id, url, tag " +
		"FROM " + tagTableName + " " +
		"WHERE id = ? " +
		"LIMIT 1"

	err := t.db.QueryRowContext(ctx, query, ID).
		Scan(&res.ID, &res.URL, &res.Tag)

	if err != nil {
		return nil, err
	}

	return &res, nil
}

func (t tagRepository) GetAll(ctx context.Context) ([]models.Tag, error) {
	var res []models.Tag

	query := "SELECT id, url, tag FROM " + tagTableName

	rows, err := t.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.Tag{}

		err = rows.Scan(
			&row.ID,
			&row.URL,
			&row.Tag,
		)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	err = rows.Err()
	if err != nil {
		return nil, err
	}

	return res, nil
}

func (t tagRepository) GetByArticleID(ctx context.Context, ID int) ([]models.Tag, error) {
	var res []models.Tag

	query := "SELECT t.id, t.url, t.tag " +
		"FROM " + articleTagTableName + " at, " + tagTableName + " t " +
		"WHERE t.id = at.tag_id AND at.article_id = ?"

	rows, err := t.db.QueryContext(ctx, query, ID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		row := models.Tag{}

		err = rows.Scan(
			&row.ID,
			&row.URL,
			&row.Tag,
		)
		if err != nil {
			return nil, err
		}

		res = append(res, row)
	}

	err = rows.Err()
	if err != nil {
		return nil, err
	}

	return res, nil
}

func (t tagRepository) IsUsed(ctx context.Context, ID int) (bool, error) {
	var count int

	query := "SELECT COUNT(tag_id) FROM " + articleTagTableName + " WHERE tag_id = ?"

	if err := t.db.QueryRowContext(ctx, query, ID).Scan(&count); err != nil {
		return false, err
	}

	return count > 0, nil
}

func (t tagRepository) Add(ctx context.Context, m models.Tag) error {
	query := "INSERT INTO " + tagTableName + " (tag, url) VALUES (?, ?)"

	_, err := t.db.ExecContext(ctx, query, m.Tag, m.URL)

	return err
}

func (t tagRepository) Update(ctx context.Context, m models.Tag) error {
	query := "UPDATE " + tagTableName + " SET tag = ?, url = ? WHERE id = ?"

	_, err := t.db.ExecContext(ctx, query, m.Tag, m.URL, m.ID)

	return err
}

func (t tagRepository) Delete(ctx context.Context, ID int) error {
	query := "DELETE FROM " + tagTableName + " WHERE id = ?"

	_, err := t.db.ExecContext(ctx, query, ID)

	return err
}