package datasql

import (
	"context"
	"fmt"
	"time"

	"gitlab.ost.ch/marketminds/marketminds/pkg/entity"
)

func (r *SqlRepository) GetAllNewsItems() ([]entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(), "SELECT * FROM marketminds.newsitem")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []entity.NewsItem
	for rows.Next() {
		var newsItem entity.NewsItem
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) GetNewsItemById(id string) (*entity.NewsItem, error) {
	var newsItem *entity.NewsItem = &entity.NewsItem{}
	err := r.pool.QueryRow(context.Background(),
		"SELECT * FROM marketminds.newsitem WHERE id = $1", id).Scan(
		&newsItem.Id,
		&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
		&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
		&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
	if err != nil {
		return nil, err
	}
	return newsItem, nil
}

func (r *SqlRepository) GetByNewsChannelId(newsChannelId int64) ([]*entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(),
		`SELECT * FROM marketminds.newsitem WHERE "newschannelId" = $1`, newsChannelId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []*entity.NewsItem
	for rows.Next() {
		var newsItem *entity.NewsItem = &entity.NewsItem{}
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) GetNewsItemByGuid(guid string) (*entity.NewsItem, error) {
	var newsItem *entity.NewsItem = &entity.NewsItem{}
	err := r.pool.QueryRow(context.Background(),
		"SELECT * FROM marketminds.newsitem WHERE guid = $1", guid).Scan(
		&newsItem.Id,
		&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
		&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
		&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
	if err != nil {
		return nil, err
	}
	return newsItem, nil
}

func (r *SqlRepository) GetNewsItemPaging(limit int, offset int) ([]*entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(),
		"SELECT * FROM marketminds.newsitem ORDER BY pubdate DESC LIMIT $1 OFFSET $2", limit, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []*entity.NewsItem
	for rows.Next() {
		var newsItem *entity.NewsItem = &entity.NewsItem{}
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) GetFirst50NewsItemsWithoutSentimentResult() ([]*entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(),
		`SELECT * FROM marketminds.newsitem WHERE id NOT IN (SELECT "newsitemId" FROM marketminds.sentimentresult) LIMIT 50`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []*entity.NewsItem
	for rows.Next() {
		var newsItem *entity.NewsItem = &entity.NewsItem{}
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) GetNewsItemsByNamedEntity(namedEntity string) ([]*entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(),
		`SELECT DISTINCT ni.* FROM marketminds.newsitem ni
		JOIN marketminds.nerresult ner ON ner."newsitemId" = ni.id
		WHERE ner."namedentityName" LIKE $1 ORDER BY ni.pubdate DESC`, namedEntity)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []*entity.NewsItem
	for rows.Next() {
		var newsItem *entity.NewsItem = &entity.NewsItem{}
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) GetFirst50NewsItemsWithoutNERResult() ([]*entity.NewsItem, error) {
	rows, err := r.pool.Query(context.Background(),
		`SELECT * FROM marketminds.newsitem WHERE id NOT IN (SELECT "newsitemId" FROM marketminds.nerresult) LIMIT 50`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var newsItems []*entity.NewsItem
	for rows.Next() {
		var newsItem *entity.NewsItem = &entity.NewsItem{}
		err := rows.Scan(
			&newsItem.Id,
			&newsItem.Title, &newsItem.Description, &newsItem.ThumbnailUrl, &newsItem.Link, &newsItem.PubDate,
			&newsItem.Category, &newsItem.Guid, &newsItem.EnclosureUrl, &newsItem.MediaCopyright,
			&newsItem.MediaTitle, &newsItem.Version, &newsItem.CreatedAt, &newsItem.NewsChannelId)
		if err != nil {
			return nil, err
		}
		newsItems = append(newsItems, newsItem)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return newsItems, nil
}

func (r *SqlRepository) CreateNewsItem(newsItem *entity.NewsItem) (int64, error) {
	query := `
	insert into marketminds.newsitem (
	  title, 
	  description, 
	  thumbnailurl, 
	  link, 
	  pubdate, 
	  category, 
	  guid, 
	  enclosureurl, 
	  mediacopyright, 
	  mediatitle, 
	  version, 
	  createdat, 
	  "newschannelId"
	)
	VALUES
	( 
	  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13
	)
	RETURNING id
`
	var id int64
	err := r.pool.QueryRow(context.Background(), query,
		newsItem.Title, newsItem.Description, newsItem.ThumbnailUrl, newsItem.Link, newsItem.PubDate,
		newsItem.Category, newsItem.Guid, newsItem.EnclosureUrl, newsItem.MediaCopyright,
		newsItem.MediaTitle, newsItem.Version, newsItem.CreatedAt, newsItem.NewsChannelId).Scan(&id)

	if err != nil {
		return 0, err
	}
	fmt.Println("News Item created")
	return id, nil
}

func (r *SqlRepository) GetLastNewsItemEntryDate(newsChannelId int64) (time.Time, error) {
	var lastDate time.Time

	query := `SELECT MAX(pubdate) FROM marketminds.newsitem WHERE "newschannelId" = $1`

	err := r.pool.QueryRow(context.Background(), query, newsChannelId).Scan(&lastDate)
	if err != nil {
		return time.Time{}, err
	}

	return lastDate, nil
}

func (r *SqlRepository) UpdateNewsItem(newsItem *entity.NewsItem) error {
	query := `
	UPDATE marketminds.newsitem
	SET 
	title=$1, 
	description=$2, 
	thumbnailurl=$3, 
	link=$4, 
	pubdate=$5, 
	category=$6, 
	guid=$7, 
	enclosureurl=$8, 
	mediacopyright=$9, 
	mediatitle=$10, 
	version=$11, 
	createdat=$12, 
	newschannelId=$13
	WHERE id=$14
	`
	_, err := r.pool.Exec(context.Background(), query,
		newsItem.Title, newsItem.Description, newsItem.ThumbnailUrl, newsItem.Link, newsItem.PubDate,
		newsItem.Category, newsItem.Guid, newsItem.EnclosureUrl, newsItem.MediaCopyright,
		newsItem.MediaTitle, newsItem.Version, newsItem.CreatedAt, newsItem.NewsChannelId,
		newsItem.Id)

	if err != nil {
		return err
	}
	return nil
}

func (r *SqlRepository) DeleteNewsItem(id int) error {
	_, err := r.pool.Exec(context.Background(), "DELETE FROM marketminds.newsitem WHERE id=$1", id)
	if err != nil {
		return err
	}
	return nil
}
