package database

import (
	"database/sql"
	"errors"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

type Pixel struct {
	ID    int64
	X     int
	Y     int
	Color int
}

// CanvasSnapshot is a materialized canvas (RGB bytes) plus the id of the last
// pixel baked into it, so replaying just needs pixels with a greater id.
type CanvasSnapshot struct {
	Data      []byte
	ThroughID int64
}

func SavePixel(db *sql.DB, userID string, x, y, color int) (int64, error) {
	res, err := db.Exec(
		"INSERT INTO canvas_pixels (user_id, x, y, color, created_at) VALUES (?, ?, ?, ?, ?)",
		userID, x, y, color, time.Now(),
	)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func LatestCanvasSnapshot(db *sql.DB) (*CanvasSnapshot, error) {
	var s CanvasSnapshot
	err := db.QueryRow("SELECT data, through_id FROM canvas_snapshots ORDER BY id DESC LIMIT 1").Scan(&s.Data, &s.ThroughID)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &s, nil
}

func PixelsAfter(db *sql.DB, throughID int64) ([]Pixel, error) {
	rows, err := db.Query("SELECT id, x, y, color FROM canvas_pixels WHERE id > ? ORDER BY id", throughID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	pixels := []Pixel{}
	for rows.Next() {
		var p Pixel
		if err := rows.Scan(&p.ID, &p.X, &p.Y, &p.Color); err != nil {
			return nil, err
		}
		pixels = append(pixels, p)
	}
	return pixels, rows.Err()
}

func CountPixelsAfter(db *sql.DB, throughID int64) (int, error) {
	var n int
	err := db.QueryRow("SELECT COUNT(*) FROM canvas_pixels WHERE id > ?", throughID).Scan(&n)
	return n, err
}

func SaveCanvasSnapshot(db *sql.DB, data []byte, throughID int64) error {
	_, err := db.Exec("INSERT INTO canvas_snapshots (data, through_id, created_at) VALUES (?, ?, ?)", data, throughID, time.Now())
	return err
}

func DeleteOldCanvasSnapshots(db *sql.DB) error {
	_, err := db.Exec("DELETE FROM canvas_snapshots WHERE id NOT IN (SELECT id FROM canvas_snapshots ORDER BY id DESC LIMIT 1)")
	return err
}