package data

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

var (
	ErrPlaceInsufficientPoints = errors.New("not enough place points")
	ErrPlaceBounds             = errors.New("pixel outside place canvas")
	ErrPlaceColor              = errors.New("invalid place color")
)

type PlacePixel struct {
	X          int       `json:"x"`
	Y          int       `json:"y"`
	ColorIndex int       `json:"color_index"`
	ClientID   string    `json:"client_id"`
	PlacedAt   time.Time `json:"placed_at"`
	Revision   int64     `json:"revision"`
}

type PlacePixelEvent struct {
	X          int       `json:"x"`
	Y          int       `json:"y"`
	ColorIndex int       `json:"color_index"`
	ClientID   string    `json:"client_id"`
	CreatedAt  time.Time `json:"created_at"`
	Revision   int64     `json:"revision"`
}

type PlaceStats struct {
	Revision        int64 `json:"revision"`
	FilledPixels    int64 `json:"filled_pixels"`
	TotalPlacements int64 `json:"total_placements"`
	Participants    int64 `json:"participants"`
}

type PlaceEarning struct {
	Kind           string    `json:"kind"`
	SourceClientID string    `json:"source_client_id"`
	TaskID         string    `json:"task_id"`
	OldScore       float64   `json:"old_score,omitempty"`
	NewScore       float64   `json:"new_score,omitempty"`
	Points         float64   `json:"points"`
	Multiplier     float64   `json:"multiplier,omitempty"`
	Units          int64     `json:"units,omitempty"`
	Detail         string    `json:"detail,omitempty"`
	CreatedAt      time.Time `json:"created_at"`
}

type PlaceWallet struct {
	ClientID        string         `json:"client_id"`
	BalancePoints   float64        `json:"balance_points"`
	EarnedPoints    float64        `json:"earned_points"`
	SpentPoints     float64        `json:"spent_points"`
	Placements      int64          `json:"placements"`
	OwnedPixels     int64          `json:"owned_pixels"`
	AvailablePixels int64          `json:"available_pixels"`
	LinkedWorkers   []string       `json:"linked_workers"`
	RecentEarnings  []PlaceEarning `json:"recent_earnings"`
}

func milliToPoints(v int64) float64 { return float64(v) / 1000.0 }

func (s *Store) PlaceRevision(ctx context.Context) (int64, error) {
	var revision int64
	err := s.DB.QueryRowContext(ctx, `SELECT revision FROM place_state WHERE id=1`).Scan(&revision)
	return revision, err
}

func (s *Store) PlacePixelsOutside(ctx context.Context, width, height int) (int64, error) {
	var n int64
	err := s.DB.QueryRowContext(ctx, `SELECT count(*) FROM place_pixels WHERE x>=? OR y>=?`, width, height).Scan(&n)
	return n, err
}

func (s *Store) PlaceSnapshot(ctx context.Context) ([]PlacePixel, PlaceStats, error) {
	var stats PlaceStats
	if err := s.DB.QueryRowContext(ctx, `SELECT revision FROM place_state WHERE id=1`).Scan(&stats.Revision); err != nil {
		return nil, stats, err
	}
	if err := s.DB.QueryRowContext(ctx, `SELECT count(*) FROM place_pixels`).Scan(&stats.FilledPixels); err != nil {
		return nil, stats, err
	}
	if err := s.DB.QueryRowContext(ctx, `SELECT count(*) FROM place_pixel_events`).Scan(&stats.TotalPlacements); err != nil {
		return nil, stats, err
	}
	if err := s.DB.QueryRowContext(ctx, `SELECT count(*) FROM place_wallets WHERE earned_milli>0 OR placements>0`).Scan(&stats.Participants); err != nil {
		return nil, stats, err
	}

	rows, err := s.DB.QueryContext(ctx, `SELECT x,y,color_index,client_id,placed_at,revision FROM place_pixels ORDER BY y,x`)
	if err != nil {
		return nil, stats, err
	}
	defer rows.Close()
	out := make([]PlacePixel, 0, stats.FilledPixels)
	for rows.Next() {
		var p PlacePixel
		var placedMS int64
		if err := rows.Scan(&p.X, &p.Y, &p.ColorIndex, &p.ClientID, &placedMS, &p.Revision); err != nil {
			return nil, stats, err
		}
		p.PlacedAt = time.UnixMilli(placedMS).UTC()
		out = append(out, p)
	}
	return out, stats, rows.Err()
}

func (s *Store) PlaceRecent(ctx context.Context, limit int) ([]PlacePixelEvent, error) {
	if limit < 1 || limit > 100 {
		limit = 30
	}
	rows, err := s.DB.QueryContext(ctx, `SELECT x,y,color_index,client_id,created_at,revision FROM place_pixel_events ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make([]PlacePixelEvent, 0, limit)
	for rows.Next() {
		var e PlacePixelEvent
		var createdMS int64
		if err := rows.Scan(&e.X, &e.Y, &e.ColorIndex, &e.ClientID, &createdMS, &e.Revision); err != nil {
			return nil, err
		}
		e.CreatedAt = time.UnixMilli(createdMS).UTC()
		out = append(out, e)
	}
	return out, rows.Err()
}

func (s *Store) PlaceChanges(ctx context.Context, after int64, limit int) ([]PlacePixel, int64, error) {
	if limit < 1 || limit > 5000 {
		limit = 2000
	}
	var revision int64
	if err := s.DB.QueryRowContext(ctx, `SELECT revision FROM place_state WHERE id=1`).Scan(&revision); err != nil {
		return nil, 0, err
	}
	rows, err := s.DB.QueryContext(ctx, `SELECT x,y,color_index,client_id,placed_at,revision FROM place_pixels WHERE revision>? ORDER BY revision LIMIT ?`, after, limit)
	if err != nil {
		return nil, revision, err
	}
	defer rows.Close()
	out := make([]PlacePixel, 0)
	for rows.Next() {
		var p PlacePixel
		var placedMS int64
		if err := rows.Scan(&p.X, &p.Y, &p.ColorIndex, &p.ClientID, &placedMS, &p.Revision); err != nil {
			return nil, revision, err
		}
		p.PlacedAt = time.UnixMilli(placedMS).UTC()
		out = append(out, p)
	}
	if err := rows.Err(); err != nil {
		return nil, revision, err
	}
	if len(out) == limit && len(out) > 0 {
		revision = out[len(out)-1].Revision
	}
	return out, revision, nil
}

func (s *Store) PlaceWallet(ctx context.Context, clientID string, pixelCostPoints int) (PlaceWallet, error) {
	now := time.Now().UTC().UnixMilli()
	if _, err := s.DB.ExecContext(ctx, `INSERT INTO place_wallets(client_id,updated_at) VALUES(?,?) ON CONFLICT(client_id) DO NOTHING`, clientID, now); err != nil {
		return PlaceWallet{}, err
	}
	var balance, earned, spent int64
	var out PlaceWallet
	out.ClientID = clientID
	out.LinkedWorkers = []string{}
	out.RecentEarnings = []PlaceEarning{}
	if err := s.DB.QueryRowContext(ctx, `SELECT balance_milli,earned_milli,spent_milli,placements FROM place_wallets WHERE client_id=?`, clientID).
		Scan(&balance, &earned, &spent, &out.Placements); err != nil {
		return out, err
	}
	out.BalancePoints = milliToPoints(balance)
	out.EarnedPoints = milliToPoints(earned)
	out.SpentPoints = milliToPoints(spent)
	if err := s.DB.QueryRowContext(ctx, `SELECT count(*) FROM place_pixels WHERE client_id=?`, clientID).Scan(&out.OwnedPixels); err != nil {
		return out, err
	}
	costMilli := int64(pixelCostPoints) * 1000
	if costMilli > 0 {
		out.AvailablePixels = balance / costMilli
	}

	workers, err := s.DB.QueryContext(ctx, `SELECT worker_client_id FROM identity_delegations WHERE owner_client_id=? ORDER BY worker_client_id`, clientID)
	if err != nil {
		return out, err
	}
	for workers.Next() {
		var id string
		if err := workers.Scan(&id); err != nil {
			workers.Close()
			return out, err
		}
		out.LinkedWorkers = append(out.LinkedWorkers, id)
	}
	if err := workers.Err(); err != nil {
		workers.Close()
		return out, err
	}
	workers.Close()

	rows, err := s.DB.QueryContext(ctx, `
		SELECT kind,source_client_id,task_id,old_score,new_score,points_milli,multiplier,units,detail,created_at FROM (
			SELECT 'progress' AS kind,source_client_id,task_id,old_score,new_score,points_milli,1.0 AS multiplier,1 AS units,'' AS detail,created_at,id*2 AS sort_id
			FROM place_progress_events WHERE owner_client_id=?
			UNION ALL
			SELECT kind,source_client_id,task_id,0.0 AS old_score,0.0 AS new_score,points_milli,multiplier,units,detail,created_at,id*2+1 AS sort_id
			FROM place_bonus_events WHERE owner_client_id=?
		) ORDER BY created_at DESC,sort_id DESC LIMIT 16`, clientID, clientID)
	if err != nil {
		return out, err
	}
	for rows.Next() {
		var e PlaceEarning
		var pointsMilli, createdMS int64
		if err := rows.Scan(&e.Kind, &e.SourceClientID, &e.TaskID, &e.OldScore, &e.NewScore, &pointsMilli, &e.Multiplier, &e.Units, &e.Detail, &createdMS); err != nil {
			rows.Close()
			return out, err
		}
		e.Points = milliToPoints(pointsMilli)
		e.CreatedAt = time.UnixMilli(createdMS).UTC()
		out.RecentEarnings = append(out.RecentEarnings, e)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return out, err
	}
	rows.Close()
	return out, nil
}

func (s *Store) PlacePixelAt(ctx context.Context, x, y int) (PlacePixel, error) {
	var p PlacePixel
	var placedMS int64
	err := s.DB.QueryRowContext(ctx, `SELECT x,y,color_index,client_id,placed_at,revision FROM place_pixels WHERE x=? AND y=?`, x, y).
		Scan(&p.X, &p.Y, &p.ColorIndex, &p.ClientID, &placedMS, &p.Revision)
	if err != nil {
		return p, err
	}
	p.PlacedAt = time.UnixMilli(placedMS).UTC()
	return p, nil
}

// PlacePixel spends points and updates the shared canvas in one transaction.
// There is intentionally no time cooldown: Hunt progress is the scarce resource.
func (s *Store) PlacePixel(ctx context.Context, clientID string, x, y, colorIndex, width, height, pixelCostPoints, paletteSize int) (PlacePixel, PlaceWallet, error) {
	if x < 0 || y < 0 || x >= width || y >= height {
		return PlacePixel{}, PlaceWallet{}, ErrPlaceBounds
	}
	if colorIndex < 0 || colorIndex >= paletteSize {
		return PlacePixel{}, PlaceWallet{}, ErrPlaceColor
	}
	costMilli := int64(pixelCostPoints) * 1000
	if costMilli <= 0 {
		return PlacePixel{}, PlaceWallet{}, fmt.Errorf("invalid place pixel cost")
	}

	tx, err := s.DB.BeginTx(ctx, nil)
	if err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	defer tx.Rollback()
	nowMS := time.Now().UTC().UnixMilli()
	if _, err := tx.ExecContext(ctx, `INSERT INTO place_wallets(client_id,updated_at) VALUES(?,?) ON CONFLICT(client_id) DO NOTHING`, clientID, nowMS); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	res, err := tx.ExecContext(ctx, `UPDATE place_wallets SET balance_milli=balance_milli-?,spent_milli=spent_milli+?,placements=placements+1,updated_at=? WHERE client_id=? AND balance_milli>=?`, costMilli, costMilli, nowMS, clientID, costMilli)
	if err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	n, _ := res.RowsAffected()
	if n != 1 {
		return PlacePixel{}, PlaceWallet{}, ErrPlaceInsufficientPoints
	}
	if _, err := tx.ExecContext(ctx, `UPDATE place_state SET revision=revision+1,updated_at=? WHERE id=1`, nowMS); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	var revision int64
	if err := tx.QueryRowContext(ctx, `SELECT revision FROM place_state WHERE id=1`).Scan(&revision); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO place_pixels(x,y,color_index,client_id,placed_at,revision) VALUES(?,?,?,?,?,?) ON CONFLICT(x,y) DO UPDATE SET color_index=excluded.color_index,client_id=excluded.client_id,placed_at=excluded.placed_at,revision=excluded.revision`, x, y, colorIndex, clientID, nowMS, revision); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO place_pixel_events(x,y,color_index,client_id,cost_milli,created_at,revision) VALUES(?,?,?,?,?,?,?)`, x, y, colorIndex, clientID, costMilli, nowMS, revision); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}
	if err := tx.Commit(); err != nil {
		return PlacePixel{}, PlaceWallet{}, err
	}

	p := PlacePixel{X: x, Y: y, ColorIndex: colorIndex, ClientID: clientID, PlacedAt: time.UnixMilli(nowMS).UTC(), Revision: revision}
	wallet, err := s.PlaceWallet(ctx, clientID, pixelCostPoints)
	return p, wallet, err
}

// placeRewardImprovementTx credits the reward identity for genuine durable
// score progress. Delegated workers resolve to their owner at the instant the
// progress is committed. A minimum of one milli-point ensures every positive
// score increase is represented, even if it is extremely small.
func (s *Store) placeRewardImprovementTx(ctx context.Context, tx *sql.Tx, taskID, sourceClientID string, oldScore, newScore float64, pointsPerScore int, nowMS int64) (string, int64, error) {
	if pointsPerScore <= 0 || newScore <= oldScore {
		return "", 0, nil
	}
	owner := sourceClientID
	var delegatedOwner string
	err := tx.QueryRowContext(ctx, `SELECT owner_client_id FROM identity_delegations WHERE worker_client_id=?`, sourceClientID).Scan(&delegatedOwner)
	switch {
	case err == nil && delegatedOwner != "":
		owner = delegatedOwner
	case errors.Is(err, sql.ErrNoRows):
		// Direct player progress credits the player identity itself.
	case err != nil:
		return "", 0, err
	}
	pointsMilli := int64((newScore-oldScore)*float64(pointsPerScore)*1000.0 + 0.5)
	if pointsMilli < 1 {
		pointsMilli = 1
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO place_wallets(client_id,balance_milli,earned_milli,updated_at) VALUES(?,?,?,?) ON CONFLICT(client_id) DO UPDATE SET balance_milli=place_wallets.balance_milli+excluded.balance_milli,earned_milli=place_wallets.earned_milli+excluded.earned_milli,updated_at=excluded.updated_at`, owner, pointsMilli, pointsMilli, nowMS); err != nil {
		return "", 0, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO place_progress_events(owner_client_id,source_client_id,task_id,old_score,new_score,points_milli,created_at) VALUES(?,?,?,?,?,?,?)`, owner, sourceClientID, taskID, oldScore, newScore, pointsMilli, nowMS); err != nil {
		return "", 0, err
	}
	return owner, pointsMilli, nil
}