package storage

import (
	"database/sql"
	"errors"
	"fmt"
)

// The mischief storefront's half of the euro border.
//
// A buyer signs in, picks a mark off the anonymous roster board, and places a
// hit. Pete records only the *intent*: it never moves money and never runs a
// single game rule. gogobee's poll loop reads the pending orders, does the real
// work against its own ledger (debit, eligibility, open the contract), and hands
// back a verdict Pete files against the order. The guid is the idempotency key
// end to end — gogobee passes it to DebitIdem and stamps it on the contract — so
// a verdict whose ack is lost on the wire can be retried without the buyer paying
// twice or the mark catching two hits for one order.

// MischiefOrder is one storefront order and its current standing.
type MischiefOrder struct {
	GUID          string `json:"guid"`
	BuyerSub      string `json:"-"`             // OIDC subject; keys "my orders", never sent to gogobee
	BuyerUsername string `json:"buyer_username"` // localpart gogobee turns into @username:server
	TargetToken   string `json:"target_token"`
	TargetName    string `json:"target_name"`
	Tier          string `json:"tier"`
	Signed        bool   `json:"signed"`
	Status        string `json:"status"`
	Detail        string `json:"detail,omitempty"`
	CreatedAt     int64  `json:"created_at"`
	UpdatedAt     int64  `json:"updated_at,omitempty"`
}

// Order states. These strings cross the wire to gogobee (it POSTs the verdict),
// so they are part of the contract — see the schema and gogobee_mischief_plan.md.
const (
	MischiefPending           = "pending"            // placed; gogobee hasn't acted yet
	MischiefPlaced            = "placed"             // gogobee debited the buyer and opened a contract
	MischiefBouncedFunds      = "bounced_funds"      // buyer couldn't afford it after all
	MischiefBouncedIneligible = "bounced_ineligible" // target no longer a valid mark
)

// validMischiefVerdict is the set of terminal states gogobee is allowed to hand
// back. An unknown verdict is a contract mismatch, not something to file blindly.
func validMischiefVerdict(status string) bool {
	switch status {
	case MischiefPlaced, MischiefBouncedFunds, MischiefBouncedIneligible:
		return true
	}
	return false
}

var ErrNoSuchOrder = errors.New("mischief: no such order")

// InsertMischiefOrder records a fresh, pending order and returns it with a new
// guid. Money and eligibility are gogobee's problem; all Pete asserts here is
// that the buyer is signed in and the form was well formed (checked by the
// caller). The guid is minted here so the buyer sees a stable reference the
// instant they submit, before gogobee has ever heard of it.
func InsertMischiefOrder(buyerSub, buyerUsername, targetToken, targetName, tier string, signed bool) (MischiefOrder, error) {
	guid, err := newGUID()
	if err != nil {
		return MischiefOrder{}, err
	}
	now := nowUnix()
	sig := 0
	if signed {
		sig = 1
	}
	if _, err := Get().Exec(
		`INSERT INTO mischief_orders
		    (guid, buyer_sub, buyer_username, target_token, target_name, tier, signed, status, created_at, updated_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		guid, buyerSub, buyerUsername, targetToken, targetName, tier, sig, MischiefPending, now, now,
	); err != nil {
		return MischiefOrder{}, fmt.Errorf("mischief: insert order: %w", err)
	}
	return MischiefOrder{
		GUID: guid, BuyerSub: buyerSub, BuyerUsername: buyerUsername,
		TargetToken: targetToken, TargetName: targetName, Tier: tier,
		Signed: signed, Status: MischiefPending, CreatedAt: now, UpdatedAt: now,
	}, nil
}

// PendingMischiefOrders is what gogobee's poll loop reads: every order still
// waiting to be acted on. There is no claimed-but-stale window like escrow has,
// because a pending order carries no intermediate state — if gogobee dies partway
// through, the order simply stays pending and is offered again next poll, and the
// guid makes the replay a no-op.
func PendingMischiefOrders(limit int) ([]MischiefOrder, error) {
	if limit <= 0 {
		limit = 100
	}
	rows, err := Get().Query(
		`SELECT guid, buyer_sub, buyer_username, target_token, target_name, tier, signed, status, COALESCE(detail, ''), created_at, updated_at
		   FROM mischief_orders
		  WHERE status = ?
		  ORDER BY created_at
		  LIMIT ?`,
		MischiefPending, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("mischief: pending orders: %w", err)
	}
	defer rows.Close()
	return scanMischiefOrders(rows)
}

// ResolveMischiefOrder files gogobee's verdict against a pending order. It is
// idempotent: gogobee's poll loop can re-offer and re-resolve the same order, so
// a verdict that arrives twice is a no-op the second time. Only a pending order
// moves; an order already in a terminal state reports what it already decided,
// which is what stops a retried verdict from overwriting the first one.
func ResolveMischiefOrder(guid, status, detail string) (MischiefOrder, error) {
	if !validMischiefVerdict(status) {
		return MischiefOrder{}, fmt.Errorf("mischief: bad verdict %q", status)
	}
	now := nowUnix()
	if _, err := Get().Exec(
		`UPDATE mischief_orders SET status = ?, detail = ?, updated_at = ?
		  WHERE guid = ? AND status = ?`,
		status, detail, now, guid, MischiefPending,
	); err != nil {
		return MischiefOrder{}, fmt.Errorf("mischief: resolve order: %w", err)
	}
	// Whether the update moved a pending order or matched nothing (missing, or
	// already terminal from an earlier verdict), the current row is the
	// authoritative answer — reading it back is the single path either way, and
	// MischiefOrderByGUID turns a missing row into ErrNoSuchOrder for the caller.
	return MischiefOrderByGUID(guid)
}

// MischiefOrderByGUID reads one order.
func MischiefOrderByGUID(guid string) (MischiefOrder, error) {
	rows, err := Get().Query(
		`SELECT guid, buyer_sub, buyer_username, target_token, target_name, tier, signed, status, COALESCE(detail, ''), created_at, updated_at
		   FROM mischief_orders WHERE guid = ?`, guid,
	)
	if err != nil {
		return MischiefOrder{}, fmt.Errorf("mischief: read order: %w", err)
	}
	defer rows.Close()
	out, err := scanMischiefOrders(rows)
	if err != nil {
		return MischiefOrder{}, err
	}
	if len(out) == 0 {
		return MischiefOrder{}, ErrNoSuchOrder
	}
	return out[0], nil
}

// MischiefOrdersByBuyer returns a buyer's own recent orders, newest first, for
// the storefront status panel. Keyed on the OIDC subject so a username change
// doesn't strand a buyer's history.
func MischiefOrdersByBuyer(buyerSub string, limit int) ([]MischiefOrder, error) {
	if limit <= 0 {
		limit = 20
	}
	rows, err := Get().Query(
		`SELECT guid, buyer_sub, buyer_username, target_token, target_name, tier, signed, status, COALESCE(detail, ''), created_at, updated_at
		   FROM mischief_orders
		  WHERE buyer_sub = ?
		  ORDER BY created_at DESC
		  LIMIT ?`,
		buyerSub, limit,
	)
	if err != nil {
		return nil, fmt.Errorf("mischief: orders by buyer: %w", err)
	}
	defer rows.Close()
	return scanMischiefOrders(rows)
}

// CountMischiefOrdersSince counts how many orders a buyer has placed since a unix
// cutoff. It backs the storefront's burst guard — the real economic caps (per
// day, per target, boss weekly) live on gogobee, this only blunts form-spam.
func CountMischiefOrdersSince(buyerSub string, since int64) (int, error) {
	var n int
	err := Get().QueryRow(
		`SELECT COUNT(*) FROM mischief_orders WHERE buyer_sub = ? AND created_at >= ?`,
		buyerSub, since,
	).Scan(&n)
	if err != nil {
		return 0, fmt.Errorf("mischief: count recent orders: %w", err)
	}
	return n, nil
}

func scanMischiefOrders(rows *sql.Rows) ([]MischiefOrder, error) {
	var out []MischiefOrder
	for rows.Next() {
		var o MischiefOrder
		var sig int
		if err := rows.Scan(&o.GUID, &o.BuyerSub, &o.BuyerUsername, &o.TargetToken,
			&o.TargetName, &o.Tier, &sig, &o.Status, &o.Detail, &o.CreatedAt, &o.UpdatedAt); err != nil {
			return nil, fmt.Errorf("mischief: scan order: %w", err)
		}
		o.Signed = sig != 0
		out = append(out, o)
	}
	return out, rows.Err()
}

// ---- user_euro: the buyer's own advisory balance -------------------------------

// ReplaceUserEuro swaps the whole balance table for a new snapshot, in one
// transaction, mirroring the roster: a buyer gogobee stopped reporting (opted
// out, deleted character) must drop out rather than keep a frozen number forever.
// Replace — never merge.
func ReplaceUserEuro(balances []MischiefBalance, snapshotAt int64) error {
	tx, err := Get().Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec(`DELETE FROM user_euro`); err != nil {
		return err
	}
	stmt, err := tx.Prepare(`INSERT INTO user_euro (username, euro, snapshot_at) VALUES (?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for _, b := range balances {
		if b.Username == "" {
			continue
		}
		if _, err := stmt.Exec(b.Username, b.Euro, snapshotAt); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// MischiefBalance is one buyer's advisory euro balance in the roster push.
type MischiefBalance struct {
	Username string  `json:"username"`
	Euro     float64 `json:"euro"`
}

// ---- mischief_tiers: the price catalog, pushed by gogobee -----------------------

// MischiefTier is one rung of the storefront's price list. gogobee is the sole
// authority on prices — it pushes the whole catalog on the roster tick so a fee
// retune reaches the storefront within a snapshot and Pete never hardcodes a
// number that can silently drift. Pete uses it only to render and to validate a
// submitted tier key; the real debit is always gogobee's, at its own price.
type MischiefTier struct {
	Key       string `json:"key"`
	Display   string `json:"display"`
	Fee       int    `json:"fee"`
	SignedFee int    `json:"signed_fee"`
	Blurb     string `json:"blurb,omitempty"`
}

// ReplaceMischiefTiers swaps the whole catalog for gogobee's latest, preserving
// the push order (grunt→boss) via an ordinal column. Replace, never merge — a
// tier gogobee dropped must disappear from the storefront.
func ReplaceMischiefTiers(tiers []MischiefTier) error {
	tx, err := Get().Begin()
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if _, err := tx.Exec(`DELETE FROM mischief_tiers`); err != nil {
		return err
	}
	stmt, err := tx.Prepare(`INSERT INTO mischief_tiers (key, display, fee, signed_fee, blurb, ordinal) VALUES (?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for i, t := range tiers {
		if t.Key == "" {
			continue
		}
		if _, err := stmt.Exec(t.Key, t.Display, t.Fee, t.SignedFee, t.Blurb, i); err != nil {
			return err
		}
	}
	return tx.Commit()
}

// MischiefTiers returns the catalog in push order. Empty (not an error) until
// gogobee has pushed one — the storefront treats that as "catalog not ready yet".
func MischiefTiers() ([]MischiefTier, error) {
	rows, err := Get().Query(
		`SELECT key, display, fee, signed_fee, COALESCE(blurb, '') FROM mischief_tiers ORDER BY ordinal`)
	if err != nil {
		return nil, fmt.Errorf("mischief: read tiers: %w", err)
	}
	defer rows.Close()
	var out []MischiefTier
	for rows.Next() {
		var t MischiefTier
		if err := rows.Scan(&t.Key, &t.Display, &t.Fee, &t.SignedFee, &t.Blurb); err != nil {
			return nil, fmt.Errorf("mischief: scan tier: %w", err)
		}
		out = append(out, t)
	}
	return out, rows.Err()
}

// MischiefTierByKey looks up one tier for validating a submitted order.
func MischiefTierByKey(key string) (MischiefTier, bool, error) {
	tiers, err := MischiefTiers()
	if err != nil {
		return MischiefTier{}, false, err
	}
	for _, t := range tiers {
		if t.Key == key {
			return t, true, nil
		}
	}
	return MischiefTier{}, false, nil
}

// UserEuro reads one buyer's advisory balance. The bool is false when gogobee has
// never reported a balance for them (never played, opted out) — the storefront
// then shows tiers without an affordability hint rather than a misleading €0.
func UserEuro(username string) (float64, bool, error) {
	var euro float64
	err := Get().QueryRow(`SELECT euro FROM user_euro WHERE username = ?`, username).Scan(&euro)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, fmt.Errorf("mischief: read user euro: %w", err)
	}
	return euro, true, nil
}