package dbi

import (
	"context"
	"database/sql"
)

type DashboardItem struct {
	UID          string
	Created      string
	LastName     string
	FirstName    string
	Title        string
	Description  string
	Hyperlink    string
	DocumentLink string
	ItemType     string
}

func GetReportedItems(conn *sql.Conn) ([]DashboardItem, error) {
	rows, err := conn.QueryContext(context.Background(), `
		SELECT s.UID, s.Created, u.LastName, u.FirstName, '' AS Title, s.Citation AS Description, s.Hyperlink, s.DocumentLink, 'Scholarship'
		FROM scholarship s JOIN users u ON u.Username = s.Username WHERE s.Status = 'reported'
		UNION ALL
		SELECT a.UID, a.Created, u.LastName, u.FirstName, a.Title, a.Description, a.Hyperlink, '' AS DocumentLink, 'Activity'
		FROM activities a JOIN users u ON u.Username = a.Username WHERE a.Status = 'reported'
		UNION ALL
		SELECT ap.UID, ap.Created, u.LastName, u.FirstName, ap.Title, ap.Description, ap.Hyperlink, '' AS DocumentLink, 'Media Appearance'
		FROM appearances ap JOIN users u ON u.Username = ap.Username WHERE ap.Status = 'reported'
		ORDER BY Created DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var items []DashboardItem
	for rows.Next() {
		var item DashboardItem
		if err := rows.Scan(&item.UID, &item.Created, &item.LastName, &item.FirstName, &item.Title, &item.Description, &item.Hyperlink, &item.DocumentLink, &item.ItemType); err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}