package store

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

	"github.com/iceBear67/simplepages/internal/cas"
)

// This file holds the queries the background jobs use: startup recovery, the
// retention sweep and the blob collector.
//
// They are kept apart from the request-path queries because they answer a
// different question. A handler works on state a transaction has just
// established; these run against whatever a crash, a restored backup or a
// half-finished sweep left behind, so each one has to be safe to run against a
// world that already disagrees with itself, and safe to run again after being
// interrupted partway.

// DeploymentRef names a deployment by the two values its directory is built
// from, which is all an orphan sweep needs to know.
type DeploymentRef struct {
	ProjectID int64
	PublicID  string
}

// AllDeploymentRefs lists every deployment the database knows about.
//
// Only the identifying pair, because the caller compares it against directory
// names: reading full rows for a sweep that will normally delete nothing would
// be a lot of scanning for no answer that changes.
func (d *DB) AllDeploymentRefs(ctx context.Context) ([]DeploymentRef, error) {
	rows, err := d.r.QueryContext(ctx, `SELECT project_id, public_id FROM deployments`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var refs []DeploymentRef
	for rows.Next() {
		var ref DeploymentRef
		if err := rows.Scan(&ref.ProjectID, &ref.PublicID); err != nil {
			return nil, err
		}
		refs = append(refs, ref)
	}
	return refs, rows.Err()
}

// DeploymentsInState lists deployments in one state across every project,
// oldest first. Recovery uses it to find the rows a previous sweep was in the
// middle of deleting.
func (d *DB) DeploymentsInState(ctx context.Context, state State, limit int) ([]*Deployment, error) {
	if limit <= 0 {
		limit = 1000
	}
	rows, err := d.r.QueryContext(ctx,
		`SELECT `+deploymentColumns+` FROM deployments WHERE state = ? ORDER BY id LIMIT ?`,
		string(state), limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*Deployment
	for rows.Next() {
		dep, err := scanDeployment(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, dep)
	}
	return out, rows.Err()
}

// InactiveDeployments lists one project's deployments that are not the one it
// serves, newest first. This is the input to the retention decision, and the
// active deployment is excluded here rather than filtered later so that no
// arithmetic on the caller's side can ever select it.
func (d *DB) InactiveDeployments(ctx context.Context, projectID int64) ([]*Deployment, error) {
	rows, err := d.r.QueryContext(ctx,
		`SELECT `+deploymentColumns+` FROM deployments
		 WHERE project_id = ? AND active = 0 ORDER BY id DESC`, projectID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*Deployment
	for rows.Next() {
		dep, err := scanDeployment(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, dep)
	}
	return out, rows.Err()
}

// ExpireStaleDeployments fails uploads that were started before cutoff and
// never finished, dropping their manifests so the blobs only they referenced
// become collectable.
//
// Nothing distinguishes a CI job that died from one that is merely slow except
// how long it has been, which is why the cutoff wants to be generous: expiring
// an upload that was still going to succeed turns a slow deploy into a failed
// one. Their blobs survive regardless — they are content-addressed, so the
// retry finds them already present and skips them.
func (d *DB) ExpireStaleDeployments(ctx context.Context, cutoff time.Time, reason string) (int, error) {
	var n int
	err := d.Tx(ctx, func(tx *sql.Tx) error {
		// Tx re-runs this on a busy database, so the count is rebuilt from
		// scratch on each attempt rather than added to.
		n = 0

		rows, err := tx.QueryContext(ctx,
			`SELECT id FROM deployments WHERE state IN (?, ?) AND created_at < ?`,
			StatePending, StateUploading, cutoff.Unix())
		if err != nil {
			return err
		}
		var ids []int64
		for rows.Next() {
			var id int64
			if err := rows.Scan(&id); err != nil {
				rows.Close()
				return err
			}
			ids = append(ids, id)
		}
		if err := rows.Err(); err != nil {
			rows.Close()
			return err
		}
		rows.Close()

		for _, id := range ids {
			// The manifest rows go first: their delete trigger is what takes the
			// refcounts back down, and it is the only reason expiry frees
			// anything.
			if _, err := tx.ExecContext(ctx,
				`DELETE FROM deployment_files WHERE deployment_id = ?`, id); err != nil {
				return err
			}
			if _, err := tx.ExecContext(ctx,
				`UPDATE deployments SET state = ?, error = ? WHERE id = ?`,
				StateFailed, truncate(reason, 1024), id); err != nil {
				return err
			}
			n++
		}
		return nil
	})
	if err != nil {
		return 0, err
	}
	return n, nil
}

// MarkDeploymentDeleting claims a deployment for deletion.
//
// The state change is committed before any file is removed, so a crash midway
// through leaves a row that says what was happening and recovery can finish the
// job. The active deployment can never be claimed: that check is here, in the
// same statement, rather than in the caller.
func (d *DB) MarkDeploymentDeleting(ctx context.Context, id int64) error {
	return d.Tx(ctx, func(tx *sql.Tx) error {
		res, err := tx.ExecContext(ctx,
			`UPDATE deployments SET state = ? WHERE id = ? AND active = 0`, StateDeleting, id)
		if err != nil {
			return err
		}
		n, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if n > 0 {
			return nil
		}
		// Nothing matched, so either the row is gone — mapErr turns that into
		// ErrNotFound — or it is the active one, which is the only condition the
		// statement excludes.
		var active bool
		if err := tx.QueryRowContext(ctx,
			`SELECT active FROM deployments WHERE id = ?`, id).Scan(&active); err != nil {
			return mapErr(err)
		}
		return fmt.Errorf("%w: this deployment is the one the project is serving", ErrConflict)
	})
}

// DeleteDeployment removes a deployment's rows once its tree is gone.
//
// The manifest rows are deleted explicitly rather than left to ON DELETE
// CASCADE. SQLite does not fire a child table's triggers for cascaded deletes
// unless recursive_triggers is on, and relying on that pragma would make every
// blob's refcount depend on a connection setting; deleting the rows here makes
// the decrement unconditional.
func (d *DB) DeleteDeployment(ctx context.Context, id int64) error {
	return d.Tx(ctx, func(tx *sql.Tx) error {
		var active bool
		if err := tx.QueryRowContext(ctx,
			`SELECT active FROM deployments WHERE id = ?`, id).Scan(&active); err != nil {
			return mapErr(err)
		}
		if active {
			return fmt.Errorf("%w: this deployment is the one the project is serving", ErrConflict)
		}
		if _, err := tx.ExecContext(ctx,
			`DELETE FROM deployment_files WHERE deployment_id = ?`, id); err != nil {
			return err
		}
		_, err := tx.ExecContext(ctx, `DELETE FROM deployments WHERE id = ?`, id)
		return err
	})
}

// EachPresentBlob calls fn for every digest the database believes is on disk.
//
// Streamed rather than returned as a slice because the caller wants to stat each
// one and a large store has a lot of them; a read here never blocks a write, so
// holding the cursor open across the filesystem calls costs nothing.
func (d *DB) EachPresentBlob(ctx context.Context, fn func(cas.Digest) error) error {
	rows, err := d.r.QueryContext(ctx, `SELECT digest FROM blobs WHERE present = 1`)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var raw []byte
		if err := rows.Scan(&raw); err != nil {
			return err
		}
		dg, err := cas.FromBytes(raw)
		if err != nil {
			return err
		}
		if err := fn(dg); err != nil {
			return err
		}
	}
	return rows.Err()
}

// MarkBlobsAbsent records that content the database claimed is on disk is not.
//
// The rows stay. A manifest may still reference them, and the fix is not to
// forget the blob but to ask for it again: the next deploy that names one of
// these digests is told to upload it, and every deployment that referenced it
// becomes deployable again as soon as one does.
func (d *DB) MarkBlobsAbsent(ctx context.Context, digests []cas.Digest) error {
	if len(digests) == 0 {
		return nil
	}
	return d.Tx(ctx, func(tx *sql.Tx) error {
		stmt, err := tx.PrepareContext(ctx, `UPDATE blobs SET present = 0 WHERE digest = ?`)
		if err != nil {
			return err
		}
		defer stmt.Close()
		for _, dg := range digests {
			if _, err := stmt.ExecContext(ctx, dg.Bytes()); err != nil {
				return err
			}
		}
		return nil
	})
}

// UnreferencedBlobs lists blobs no manifest has referenced since before cutoff.
//
// Both halves of the condition matter. A refcount of zero says nothing points at
// the content now; the cutoff adds that nothing has pointed at it for a while,
// which is what gives a request that has already resolved a digest and is about
// to open it time to finish. Oldest first, so a backlog drains in a stable order
// rather than the collector revisiting the same rows every sweep.
func (d *DB) UnreferencedBlobs(ctx context.Context, cutoff time.Time, limit int) ([]Blob, error) {
	if limit <= 0 {
		limit = 5000
	}
	rows, err := d.r.QueryContext(ctx, `
		SELECT digest, size, present FROM blobs
		WHERE refcount = 0 AND last_ref_at < ?
		ORDER BY last_ref_at LIMIT ?`, cutoff.Unix(), limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []Blob
	for rows.Next() {
		var b Blob
		var raw []byte
		if err := rows.Scan(&raw, &b.Size, &b.Present); err != nil {
			return nil, err
		}
		if b.Digest, err = cas.FromBytes(raw); err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// DeleteBlob removes one blob's row and its content together, reporting whether
// there was anything to remove.
//
// remove runs inside the write transaction, and that is the whole point of this
// signature. There is exactly one write connection, so no manifest can be
// accepted between the row disappearing and the file doing so — which closes the
// window where a deployment could come to reference content that was already on
// its way out. remove has to be idempotent, because Tx re-runs its function on a
// busy database; cas.Store.Remove is, deliberately.
//
// A blob that gained a reference since it was listed is left alone and reported
// as false. If remove fails the row survives with it, and the next sweep finds
// the pair again.
func (d *DB) DeleteBlob(ctx context.Context, digest cas.Digest, remove func() error) (bool, error) {
	var deleted bool
	err := d.Tx(ctx, func(tx *sql.Tx) error {
		deleted = false
		res, err := tx.ExecContext(ctx,
			`DELETE FROM blobs WHERE digest = ? AND refcount = 0`, digest.Bytes())
		if err != nil {
			return err
		}
		n, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if n == 0 {
			return nil
		}
		if err := remove(); err != nil {
			return err
		}
		deleted = true
		return nil
	})
	return deleted, err
}