package app

import (
	"context"

	"spread/internal/spread"
)

// BOMSummary is one saved bill of materials, for listings.
type BOMSummary struct {
	ID              int64
	Ref             string
	SubscriberID    int64
	SubscriberName  string
	UploadedByEmail string
	Filename        string
	TotalSavings    float64
	OurCut          float64
	NetSavings      float64
	Lines           int
	CreatedAt       string
}

// BOMLine is one re-quoted line of a saved BOM.
type BOMLine struct {
	Line        int
	PartNumber  string
	Description string
	Quantity    float64
	UnitPrice   float64
	SpreadPrice float64
	LineSaving  float64
	Matched     bool
}

// BOMDetail is a saved BOM plus its lines, for the detail view.
type BOMDetail struct {
	BOMSummary
	Items []BOMLine
}

// saveBOM persists an analyzed report under a subscriber.
func (a *App) saveBOM(ctx context.Context, subscriberID, uploadedBy int64, rep spread.Report, filename string) (int64, error) {
	tx, err := a.pool.Begin(ctx)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback(ctx)

	var id int64
	err = tx.QueryRow(ctx,
		`INSERT INTO boms (subscriber_id, uploaded_by, ref, filename, total_savings, our_cut, net_savings)
		 VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id`,
		subscriberID, uploadedBy, rep.BOMRef, filename,
		rep.Totals.Savings, rep.Totals.OurCut, rep.Totals.NetSavings,
	).Scan(&id)
	if err != nil {
		return 0, err
	}

	for _, l := range rep.Lines {
		if _, err := tx.Exec(ctx,
			`INSERT INTO bom_lines
			   (bom_id, line, part_number, description, quantity, unit_price, spread_price, line_saving, matched)
			 VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)`,
			id, l.Line, l.PartNumber, l.Description, l.Quantity, l.UnitPrice, l.SpreadPrice, l.LineSaving, l.Matched,
		); err != nil {
			return 0, err
		}
	}
	return id, tx.Commit(ctx)
}

const bomSelect = `
	SELECT b.id, b.ref, b.subscriber_id, s.name, coalesce(u.email, ''), b.filename,
	       b.total_savings, b.our_cut, b.net_savings,
	       (SELECT count(*) FROM bom_lines l WHERE l.bom_id = b.id),
	       to_char(b.created_at, 'YYYY-MM-DD HH24:MI')
	  FROM boms b
	  JOIN subscribers s ON s.id = b.subscriber_id
	  LEFT JOIN users u ON u.id = b.uploaded_by`

func scanSummary(row interface {
	Scan(...any) error
}) (BOMSummary, error) {
	var b BOMSummary
	err := row.Scan(&b.ID, &b.Ref, &b.SubscriberID, &b.SubscriberName, &b.UploadedByEmail,
		&b.Filename, &b.TotalSavings, &b.OurCut, &b.NetSavings, &b.Lines, &b.CreatedAt)
	return b, err
}

// listBOMs returns saved BOMs, newest first. A nil subscriberID lists every
// subscriber's BOMs (admin review); otherwise only that subscriber's.
func (a *App) listBOMs(ctx context.Context, subscriberID *int64) ([]BOMSummary, error) {
	q := bomSelect
	args := []any{}
	if subscriberID != nil {
		q += ` WHERE b.subscriber_id = $1`
		args = append(args, *subscriberID)
	}
	q += ` ORDER BY b.created_at DESC, b.id DESC`

	rows, err := a.pool.Query(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []BOMSummary
	for rows.Next() {
		b, err := scanSummary(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

func (a *App) getBOM(ctx context.Context, id int64) (BOMDetail, error) {
	var d BOMDetail
	sum, err := scanSummary(a.pool.QueryRow(ctx, bomSelect+` WHERE b.id = $1`, id))
	if err != nil {
		return d, err
	}
	d.BOMSummary = sum

	rows, err := a.pool.Query(ctx,
		`SELECT line, part_number, description, quantity, unit_price, spread_price, line_saving, matched
		   FROM bom_lines WHERE bom_id = $1 ORDER BY line`, id)
	if err != nil {
		return d, err
	}
	defer rows.Close()
	for rows.Next() {
		var l BOMLine
		if err := rows.Scan(&l.Line, &l.PartNumber, &l.Description, &l.Quantity,
			&l.UnitPrice, &l.SpreadPrice, &l.LineSaving, &l.Matched); err != nil {
			return d, err
		}
		d.Items = append(d.Items, l)
	}
	return d, rows.Err()
}