package service

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log/slog"
	"strconv"
	"strings"
	"time"

	"github.com/TopherMayor/unified-media-manager/internal/db"
	"github.com/jackc/pgx/v5"
)

type Media struct {
	ID                   int64           `json:"id"`
	MediaType            string          `json:"media_type"`
	Title                string          `json:"title"`
	SortTitle            string          `json:"sort_title"`
	OriginalTitle        *string         `json:"original_title,omitempty"`
	Overview             *string         `json:"overview,omitempty"`
	Year                 *int            `json:"year,omitempty"`
	Status               string          `json:"status"`
	Monitored            bool            `json:"monitored"`
	ExternalIDs          json.RawMessage `json:"external_ids"`
	Metadata             json.RawMessage `json:"metadata"`
	Images               json.RawMessage `json:"images"`
	QualityProfileID     *int64          `json:"quality_profile_id,omitempty"`
	RootFolderID         *int64          `json:"root_folder_id,omitempty"`
	ReleaseDate          *time.Time      `json:"release_date,omitempty"`
	CurrentQuality       json.RawMessage `json:"current_quality,omitempty"`
	DesiredQuality       json.RawMessage `json:"desired_quality,omitempty"`
	QualityUpgradeNeeded bool            `json:"quality_upgrade_needed"`
	AddedAt              time.Time       `json:"added_at"`
	LastSearchAt         *time.Time      `json:"last_search_at,omitempty"`
	UpdatedAt            time.Time       `json:"updated_at"`
}

type MediaFile struct {
	ID              int64           `json:"id"`
	MediaID         int64           `json:"media_id"`
	Path            string          `json:"path"`
	OriginalPath    *string         `json:"original_path,omitempty"`
	FileName        string          `json:"file_name"`
	FileSize        int64           `json:"file_size"`
	Quality         json.RawMessage `json:"quality"`
	Codec           *string         `json:"codec,omitempty"`
	Resolution      *string         `json:"resolution,omitempty"`
	Source          *string         `json:"source,omitempty"`
	TranscodeStatus string          `json:"transcode_status"`
	CreatedAt       time.Time       `json:"created_at"`
}

type MediaRelation struct {
	ID       int64  `json:"id"`
	ParentID int64  `json:"parent_id"`
	ChildID  int64  `json:"child_id"`
	Relation string `json:"relation"`
	Position *int   `json:"position,omitempty"`
	Season   *int   `json:"season,omitempty"`
}

type MediaDetail struct {
	Media     Media           `json:"media"`
	Files     []MediaFile     `json:"files"`
	Relations []MediaRelation `json:"relations"`
}

type MediaFilters struct {
	MediaType string
	Status    string
	Monitored string
	Query     string
	Tag       string
	Page      int
	PageSize  int
}

type CreateMediaRequest struct {
	MediaType        string          `json:"media_type"`
	Title            string          `json:"title"`
	SortTitle        string          `json:"sort_title,omitempty"`
	OriginalTitle    *string         `json:"original_title,omitempty"`
	Overview         *string         `json:"overview,omitempty"`
	Year             *int            `json:"year,omitempty"`
	ReleaseDate      *time.Time      `json:"release_date,omitempty"`
	Status           string          `json:"status"`
	Monitored        bool            `json:"monitored"`
	ExternalIDs      json.RawMessage `json:"external_ids,omitempty"`
	Metadata         json.RawMessage `json:"metadata,omitempty"`
	Images           json.RawMessage `json:"images,omitempty"`
	QualityProfileID *int64          `json:"quality_profile_id,omitempty"`
	RootFolderID     *int64          `json:"root_folder_id,omitempty"`
	CurrentQuality   json.RawMessage `json:"current_quality,omitempty"`
	DesiredQuality   json.RawMessage `json:"desired_quality,omitempty"`
}

type UpdateMediaRequest struct {
	Title            *string         `json:"title,omitempty"`
	SortTitle        *string         `json:"sort_title,omitempty"`
	OriginalTitle    *string         `json:"original_title,omitempty"`
	Overview         *string         `json:"overview,omitempty"`
	Year             *int            `json:"year,omitempty"`
	Status           *string         `json:"status,omitempty"`
	Monitored        *bool           `json:"monitored,omitempty"`
	ExternalIDs      json.RawMessage `json:"external_ids,omitempty"`
	Metadata         json.RawMessage `json:"metadata,omitempty"`
	Images           json.RawMessage `json:"images,omitempty"`
	QualityProfileID *int64          `json:"quality_profile_id,omitempty"`
	RootFolderID     *int64          `json:"root_folder_id,omitempty"`
	CurrentQuality   json.RawMessage `json:"current_quality,omitempty"`
	DesiredQuality   json.RawMessage `json:"desired_quality,omitempty"`
}

const mediaColumns = `id, media_type, title, sort_title, original_title, overview, year,
	release_date,
	status, monitored, external_ids, metadata, images, quality_profile_id, root_folder_id,
	current_quality, desired_quality,
	has_files,
	CASE
		WHEN has_files AND desired_quality IS NOT NULL
			AND current_quality IS NOT NULL
			AND current_quality::text != desired_quality::text
		THEN true
		ELSE false
	END AS quality_upgrade_needed,
	added_at, last_search_at, updated_at`

type MediaService struct {
	db *db.DB
}

func NewMediaService(database *db.DB) *MediaService {
	return &MediaService{db: database}
}

func scanMedia(scanner interface{ Scan(...interface{}) error }) (*Media, error) {
	var m Media
	var origTitle, overview sql.NullString
	var year sql.NullInt64
	var releaseDate sql.NullTime
	var qpID, rfID sql.NullInt64
	var lastSearchAt sql.NullTime
	var hasFiles bool

	err := scanner.Scan(&m.ID, &m.MediaType, &m.Title, &m.SortTitle, &origTitle, &overview, &year,
		&releaseDate,
		&m.Status, &m.Monitored, &m.ExternalIDs, &m.Metadata, &m.Images, &qpID, &rfID,
		&m.CurrentQuality, &m.DesiredQuality, &hasFiles, &m.QualityUpgradeNeeded, &m.AddedAt, &lastSearchAt, &m.UpdatedAt)
	if err != nil {
		return nil, err
	}
	if origTitle.Valid {
		m.OriginalTitle = &origTitle.String
	}
	if overview.Valid {
		m.Overview = &overview.String
	}
	if year.Valid {
		y := int(year.Int64)
		m.Year = &y
	}
	if releaseDate.Valid {
		m.ReleaseDate = &releaseDate.Time
	}
	if qpID.Valid {
		m.QualityProfileID = &qpID.Int64
	}
	if rfID.Valid {
		m.RootFolderID = &rfID.Int64
	}
	if lastSearchAt.Valid {
		m.LastSearchAt = &lastSearchAt.Time
	}
	return &m, nil
}

type mediaWithTotal struct {
	Media
	total int
}

func scanMediaRowWithTotal(scanner interface{ Scan(...interface{}) error }) (*mediaWithTotal, error) {
	var m Media
	var origTitle, overview sql.NullString
	var year sql.NullInt64
	var releaseDate sql.NullTime
	var qpID, rfID sql.NullInt64
	var lastSearchAt sql.NullTime
	var hasFiles bool
	var total int

	err := scanner.Scan(&m.ID, &m.MediaType, &m.Title, &m.SortTitle, &origTitle, &overview, &year,
		&releaseDate,
		&m.Status, &m.Monitored, &m.ExternalIDs, &m.Metadata, &m.Images, &qpID, &rfID,
		&m.CurrentQuality, &m.DesiredQuality, &hasFiles, &m.QualityUpgradeNeeded, &m.AddedAt, &lastSearchAt, &m.UpdatedAt,
		&total)
	if err != nil {
		return nil, err
	}
	if origTitle.Valid {
		m.OriginalTitle = &origTitle.String
	}
	if overview.Valid {
		m.Overview = &overview.String
	}
	if year.Valid {
		y := int(year.Int64)
		m.Year = &y
	}
	if releaseDate.Valid {
		m.ReleaseDate = &releaseDate.Time
	}
	if qpID.Valid {
		m.QualityProfileID = &qpID.Int64
	}
	if rfID.Valid {
		m.RootFolderID = &rfID.Int64
	}
	if lastSearchAt.Valid {
		m.LastSearchAt = &lastSearchAt.Time
	}
	return &mediaWithTotal{Media: m, total: total}, nil
}

func scanMediaRows(rows pgx.Rows) ([]Media, error) {
	var results []Media
	for rows.Next() {
		m, err := scanMedia(rows)
		if err != nil {
			return nil, fmt.Errorf("scan media row: %w", err)
		}
		results = append(results, *m)
	}
	return results, nil
}

func buildMediaFilters(filters *MediaFilters) *QueryBuilder {
	qb := NewQueryBuilder(1)
	qb.AddLiteral("deleted_at IS NULL")

	if filters.MediaType != "" {
		qb.Add("media_type = $%d", filters.MediaType)
	}
	if filters.Status != "" {
		qb.Add("status = $%d", filters.Status)
	}
	if filters.Monitored != "" {
		qb.Add("monitored = $%d", filters.Monitored == "true")
	}
	return qb
}

func (s *MediaService) List(ctx context.Context, filters MediaFilters) ([]Media, int, error) {
	qb := buildMediaFilters(&filters)

	query := fmt.Sprintf("SELECT %s, COUNT(*) OVER() AS total FROM media%s ORDER BY sort_title LIMIT $%d OFFSET $%d",
		mediaColumns, qb.Where(), qb.Idx(), qb.Idx()+1)
	args := append(qb.Args(), filters.PageSize, (filters.Page-1)*filters.PageSize)

	rows, err := s.db.Pool.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("list media: %w", err)
	}
	defer rows.Close()

	var items []Media
	var total int
	for rows.Next() {
		m, err := scanMediaRowWithTotal(rows)
		if err != nil {
			return nil, 0, fmt.Errorf("scan media row: %w", err)
		}
		if total == 0 {
			total = m.total
		}
		items = append(items, m.Media)
	}

	return items, total, nil
}

func (s *MediaService) GetByID(ctx context.Context, id int64, mediaType string) (*MediaDetail, error) {
	qb := NewQueryBuilder(1)
	qb.AddLiteral("deleted_at IS NULL")
	qb.Add("id = $%d", id)
	if mediaType != "" {
		qb.Add("media_type = $%d", mediaType)
	}

	row := s.db.Pool.QueryRow(ctx,
		"SELECT "+mediaColumns+" FROM media"+qb.Where(), qb.Args()...)

	m, err := scanMedia(row)
	if err != nil {
		return nil, fmt.Errorf("get media: %w", err)
	}

	detail := &MediaDetail{Media: *m}

	fileRows, err := s.db.Pool.Query(ctx,
		`SELECT id, media_id, path, original_path, file_name, file_size, quality, codec, resolution, source, transcode_status, created_at
		 FROM media_files WHERE media_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC`, id)
	if err == nil {
		defer fileRows.Close()
		for fileRows.Next() {
			var f MediaFile
			var origPath, codec, resolution, source sql.NullString
			if err := fileRows.Scan(&f.ID, &f.MediaID, &f.Path, &origPath, &f.FileName, &f.FileSize,
				&f.Quality, &codec, &resolution, &source, &f.TranscodeStatus, &f.CreatedAt); err != nil {
				slog.Error("failed to scan media file", "error", err)
				continue
			}
			if origPath.Valid {
				f.OriginalPath = &origPath.String
			}
			if codec.Valid {
				f.Codec = &codec.String
			}
			if resolution.Valid {
				f.Resolution = &resolution.String
			}
			if source.Valid {
				f.Source = &source.String
			}
			detail.Files = append(detail.Files, f)
		}
	}

	relRows, err := s.db.Pool.Query(ctx,
		`SELECT id, parent_id, child_id, relation, position, season
		 FROM media_relations WHERE parent_id = $1 OR child_id = $1 ORDER BY relation, position`, id)
	if err == nil {
		defer relRows.Close()
		for relRows.Next() {
			var r MediaRelation
			if err := relRows.Scan(&r.ID, &r.ParentID, &r.ChildID, &r.Relation, &r.Position, &r.Season); err != nil {
				slog.Error("failed to scan media relation", "error", err)
				continue
			}
			detail.Relations = append(detail.Relations, r)
		}
	}

	return detail, nil
}

func (s *MediaService) Create(ctx context.Context, req CreateMediaRequest) (int64, error) {
	if req.SortTitle == "" {
		req.SortTitle = req.Title
	}
	if req.Status == "" {
		req.Status = "unavailable"
	}
	if req.ExternalIDs == nil {
		req.ExternalIDs = json.RawMessage("{}")
	}
	if req.Metadata == nil {
		req.Metadata = json.RawMessage("{}")
	}
	if req.Images == nil {
		req.Images = json.RawMessage("[]")
	}

	var id int64
	err := s.db.Pool.QueryRow(ctx,
		`INSERT INTO media (media_type, title, sort_title, original_title, overview, year,
		 release_date,
		 status, monitored, external_ids, metadata, images, quality_profile_id, root_folder_id,
		 current_quality, desired_quality)
		 VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16) RETURNING id`,
		req.MediaType, req.Title, req.SortTitle, req.OriginalTitle, req.Overview, req.Year,
		req.ReleaseDate,
		req.Status, req.Monitored, req.ExternalIDs, req.Metadata, req.Images,
		req.QualityProfileID, req.RootFolderID, req.CurrentQuality, req.DesiredQuality).Scan(&id)
	if err != nil {
		return 0, fmt.Errorf("create media: %w", err)
	}

	return id, nil
}

func (s *MediaService) Update(ctx context.Context, id int64, mediaType string, req UpdateMediaRequest) error {
	var setClauses []string
	var args []interface{}
	idx := 1

	addCol := func(col string, val interface{}) {
		setClauses = append(setClauses, fmt.Sprintf("%s = $%d", col, idx))
		args = append(args, val)
		idx++
	}

	if req.Title != nil {
		addCol("title", *req.Title)
	}
	if req.SortTitle != nil {
		addCol("sort_title", *req.SortTitle)
	}
	if req.OriginalTitle != nil {
		addCol("original_title", *req.OriginalTitle)
	}
	if req.Overview != nil {
		addCol("overview", *req.Overview)
	}
	if req.Year != nil {
		addCol("year", *req.Year)
	}
	if req.Status != nil {
		addCol("status", *req.Status)
	}
	if req.Monitored != nil {
		addCol("monitored", *req.Monitored)
	}
	if req.ExternalIDs != nil {
		addCol("external_ids", req.ExternalIDs)
	}
	if req.Metadata != nil {
		addCol("metadata", req.Metadata)
	}
	if req.Images != nil {
		addCol("images", req.Images)
	}
	if req.QualityProfileID != nil {
		addCol("quality_profile_id", *req.QualityProfileID)
	}
	if req.RootFolderID != nil {
		addCol("root_folder_id", *req.RootFolderID)
	}
	if req.CurrentQuality != nil {
		addCol("current_quality", req.CurrentQuality)
	}
	if req.DesiredQuality != nil {
		addCol("desired_quality", req.DesiredQuality)
	}

	if len(setClauses) == 0 {
		return fmt.Errorf("no fields to update")
	}

	addCol("updated_at", time.Now())

	query := fmt.Sprintf("UPDATE media SET %s WHERE id = $%d AND deleted_at IS NULL",
		strings.Join(setClauses, ", "), idx)
	args = append(args, id)

	if mediaType != "" {
		query += fmt.Sprintf(" AND media_type = $%d", idx+1)
		args = append(args, mediaType)
	}

	tag, err := s.db.Pool.Exec(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("update media: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("media not found")
	}

	return nil
}

func (s *MediaService) Delete(ctx context.Context, id int64, mediaType string) error {
	query := "UPDATE media SET deleted_at = NOW(), updated_at = NOW() WHERE id = $1 AND deleted_at IS NULL"
	args := []interface{}{id}

	if mediaType != "" {
		query += " AND media_type = $2"
		args = append(args, mediaType)
	}

	tag, err := s.db.Pool.Exec(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("delete media: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("media not found")
	}

	return nil
}

func (s *MediaService) Search(ctx context.Context, filters MediaFilters) ([]Media, int, error) {
	qb := NewQueryBuilder(1)
	qb.AddLiteral("deleted_at IS NULL")

	if filters.Query != "" {
		qb.Add("to_tsvector('english', coalesce(title, '')) @@ plainto_tsquery('english', $%d)", filters.Query)
	}
	if filters.MediaType != "" {
		qb.Add("media_type = $%d", filters.MediaType)
	}
	if filters.Status != "" {
		qb.Add("status = $%d", filters.Status)
	}
	if filters.Tag != "" {
		qb.Add("id IN (SELECT mt.media_id FROM media_tags mt JOIN tags t ON mt.tag_id = t.id WHERE t.name = $%d)", filters.Tag)
	}

	query := fmt.Sprintf("SELECT %s, COUNT(*) OVER() AS total FROM media%s ORDER BY sort_title LIMIT $%d OFFSET $%d",
		mediaColumns, qb.Where(), qb.Idx(), qb.Idx()+1)
	args := append(qb.Args(), filters.PageSize, (filters.Page-1)*filters.PageSize)

	rows, err := s.db.Pool.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("search media: %w", err)
	}
	defer rows.Close()

	var items []Media
	var total int
	for rows.Next() {
		m, err := scanMediaRowWithTotal(rows)
		if err != nil {
			return nil, 0, fmt.Errorf("scan search results: %w", err)
		}
		if total == 0 {
			total = m.total
		}
		items = append(items, m.Media)
	}

	return items, total, nil
}

func (s *MediaService) SearchMissing(ctx context.Context, filters MediaFilters) ([]Media, int, error) {
	qb := NewQueryBuilder(1)
	qb.AddLiteral("monitored = true")
	qb.AddLiteral("status = 'unavailable'")
	qb.AddLiteral("deleted_at IS NULL")

	if filters.MediaType != "" {
		qb.Add("media_type = $%d", filters.MediaType)
	}

	query := fmt.Sprintf("SELECT %s, COUNT(*) OVER() AS total FROM media%s ORDER BY sort_title LIMIT $%d OFFSET $%d",
		mediaColumns, qb.Where(), qb.Idx(), qb.Idx()+1)
	args := append(qb.Args(), filters.PageSize, (filters.Page-1)*filters.PageSize)

	rows, err := s.db.Pool.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("query missing media: %w", err)
	}
	defer rows.Close()

	var items []Media
	var total int
	for rows.Next() {
		m, err := scanMediaRowWithTotal(rows)
		if err != nil {
			return nil, 0, fmt.Errorf("scan missing media: %w", err)
		}
		if total == 0 {
			total = m.total
		}
		items = append(items, m.Media)
	}

	return items, total, nil
}

func (s *MediaService) SearchUpgrades(ctx context.Context, filters MediaFilters) ([]Media, int, error) {
	qb := NewQueryBuilder(1)
	qb.AddLiteral("deleted_at IS NULL")
	qb.AddLiteral("monitored = true")
	qb.AddLiteral("has_files = true")
	qb.AddLiteral("current_quality IS NOT NULL")
	qb.AddLiteral("desired_quality IS NOT NULL")
	qb.AddLiteral("current_quality::text != desired_quality::text")

	if filters.MediaType != "" {
		qb.Add("media_type = $%d", filters.MediaType)
	}

	query := fmt.Sprintf("SELECT %s, COUNT(*) OVER() AS total FROM media%s ORDER BY sort_title LIMIT $%d OFFSET $%d",
		mediaColumns, qb.Where(), qb.Idx(), qb.Idx()+1)
	args := append(qb.Args(), filters.PageSize, (filters.Page-1)*filters.PageSize)

	rows, err := s.db.Pool.Query(ctx, query, args...)
	if err != nil {
		return nil, 0, fmt.Errorf("query upgrades: %w", err)
	}
	defer rows.Close()

	var items []Media
	var total int
	for rows.Next() {
		m, err := scanMediaRowWithTotal(rows)
		if err != nil {
			return nil, 0, fmt.Errorf("scan upgrades: %w", err)
		}
		if total == 0 {
			total = m.total
		}
		items = append(items, m.Media)
	}

	return items, total, nil
}

func CalcTotalPages(total, pageSize int) int {
	totalPages := total / pageSize
	if total%pageSize > 0 {
		totalPages++
	}
	return totalPages
}

func ParsePagination(pageStr, pageSizeStr string) (page, pageSize int) {
	page, _ = strconv.Atoi(pageStr)
	if page < 1 {
		page = 1
	}
	pageSize, _ = strconv.Atoi(pageSizeStr)
	if pageSize < 1 || pageSize > 100 {
		pageSize = 50
	}
	return
}