package store

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

// Project is a row of the projects table.
//
// The serving-related fields (IndexFile, NotFoundFile, SPAFallback,
// CacheControl) are copied into the in-memory site registry; the limits are
// enforced when a manifest is accepted.
type Project struct {
	ID           int64
	Name         string
	DisplayName  string
	IndexFile    string
	NotFoundFile string // "" means no custom 404 document
	SPAFallback  bool
	CacheControl string

	RetentionCount  int
	RetentionGraceS int
	MaxFiles        int
	MaxFileBytes    int64
	MaxTotalBytes   int64

	CreatedAt time.Time
	UpdatedAt time.Time
}

// DefaultProject returns a project carrying the same defaults the schema does,
// as the starting point for a create request.
func DefaultProject(name string) *Project {
	return &Project{
		Name:            name,
		IndexFile:       "index.html",
		CacheControl:    "public, max-age=0, must-revalidate",
		RetentionCount:  10,
		RetentionGraceS: 3600,
		MaxFiles:        50000,
		MaxFileBytes:    256 << 20,
		MaxTotalBytes:   2 << 30,
	}
}

const projectColumns = `id, name, display_name, index_file, not_found_file, spa_fallback,
	cache_control, retention_count, retention_grace_s, max_files, max_file_bytes,
	max_total_bytes, created_at, updated_at`

type rowScanner interface {
	Scan(dest ...any) error
}

func scanProject(row rowScanner) (*Project, error) {
	var p Project
	var notFound sql.NullString
	var created, updated int64
	err := row.Scan(&p.ID, &p.Name, &p.DisplayName, &p.IndexFile, &notFound, &p.SPAFallback,
		&p.CacheControl, &p.RetentionCount, &p.RetentionGraceS, &p.MaxFiles, &p.MaxFileBytes,
		&p.MaxTotalBytes, &created, &updated)
	if err != nil {
		return nil, mapErr(err)
	}
	p.NotFoundFile = notFound.String
	p.CreatedAt = time.Unix(created, 0).UTC()
	p.UpdatedAt = time.Unix(updated, 0).UTC()
	return &p, nil
}

// CreateProject inserts p and fills in its ID and timestamps. Name uniqueness is
// enforced by the schema, so a duplicate returns ErrExists rather than racing.
func (d *DB) CreateProject(ctx context.Context, p *Project) error {
	now := unixNow()
	return d.Tx(ctx, func(tx *sql.Tx) error {
		res, err := tx.ExecContext(ctx, `
			INSERT INTO projects (name, display_name, index_file, not_found_file, spa_fallback,
			                      cache_control, retention_count, retention_grace_s, max_files,
			                      max_file_bytes, max_total_bytes, created_at, updated_at)
			VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`,
			p.Name, p.DisplayName, p.IndexFile, nullString(p.NotFoundFile), p.SPAFallback,
			p.CacheControl, p.RetentionCount, p.RetentionGraceS, p.MaxFiles,
			p.MaxFileBytes, p.MaxTotalBytes, now, now)
		if err != nil {
			return mapErr(err)
		}
		id, err := res.LastInsertId()
		if err != nil {
			return err
		}
		p.ID = id
		p.CreatedAt = time.Unix(now, 0).UTC()
		p.UpdatedAt = p.CreatedAt
		return nil
	})
}

// ProjectByName looks a project up by its URL name.
func (d *DB) ProjectByName(ctx context.Context, name string) (*Project, error) {
	return scanProject(d.r.QueryRowContext(ctx,
		`SELECT `+projectColumns+` FROM projects WHERE name = ?`, name))
}

// ProjectByID looks a project up by its primary key.
func (d *DB) ProjectByID(ctx context.Context, id int64) (*Project, error) {
	return scanProject(d.r.QueryRowContext(ctx,
		`SELECT `+projectColumns+` FROM projects WHERE id = ?`, id))
}

// ListProjects returns up to limit projects ordered by name, starting after the
// cursor. The cursor is the last name returned, which is stable under
// concurrent inserts in a way that an offset is not.
func (d *DB) ListProjects(ctx context.Context, limit int, cursor string) (projects []*Project, next string, err error) {
	if limit <= 0 || limit > 500 {
		limit = 100
	}
	// One extra row tells us whether another page exists without a second query.
	rows, err := d.r.QueryContext(ctx,
		`SELECT `+projectColumns+` FROM projects WHERE name > ? ORDER BY name LIMIT ?`,
		cursor, limit+1)
	if err != nil {
		return nil, "", err
	}
	defer rows.Close()
	for rows.Next() {
		p, err := scanProject(rows)
		if err != nil {
			return nil, "", err
		}
		projects = append(projects, p)
	}
	if err := rows.Err(); err != nil {
		return nil, "", err
	}
	if len(projects) > limit {
		projects = projects[:limit]
		next = projects[len(projects)-1].Name
	}
	return projects, next, nil
}

// AllProjects returns every project, for building the in-memory registry at
// startup. The registry holds them all anyway, so paging here would be theatre.
func (d *DB) AllProjects(ctx context.Context) ([]*Project, error) {
	rows, err := d.r.QueryContext(ctx, `SELECT `+projectColumns+` FROM projects ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*Project
	for rows.Next() {
		p, err := scanProject(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

// UpdateProject writes p's mutable fields back. Name and ID are immutable: a
// rename would invalidate every deployed URL and every cached symlink, and the
// API offers delete-and-recreate instead.
func (d *DB) UpdateProject(ctx context.Context, p *Project) error {
	now := unixNow()
	return d.Tx(ctx, func(tx *sql.Tx) error {
		res, err := tx.ExecContext(ctx, `
			UPDATE projects SET display_name = ?, index_file = ?, not_found_file = ?,
			       spa_fallback = ?, cache_control = ?, retention_count = ?,
			       retention_grace_s = ?, max_files = ?, max_file_bytes = ?,
			       max_total_bytes = ?, updated_at = ?
			WHERE id = ?`,
			p.DisplayName, p.IndexFile, nullString(p.NotFoundFile), p.SPAFallback,
			p.CacheControl, p.RetentionCount, p.RetentionGraceS, p.MaxFiles,
			p.MaxFileBytes, p.MaxTotalBytes, now, p.ID)
		if err != nil {
			return mapErr(err)
		}
		n, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if n == 0 {
			return ErrNotFound
		}
		p.UpdatedAt = time.Unix(now, 0).UTC()
		return nil
	})
}

// DeleteProject removes a project and, by cascade, its keys, deployments and
// manifest rows. Blob refcounts fall as the manifest rows go, so the next GC
// pass reclaims the content.
//
// The caller is responsible for the parts the database does not know about: the
// registry entry, the webroot symlink and the assembled directories.
func (d *DB) DeleteProject(ctx context.Context, id int64) error {
	return d.Tx(ctx, func(tx *sql.Tx) error {
		// Delete the manifest rows explicitly rather than trusting the cascade to
		// fire the refcount triggers (see the schema comment).
		if _, err := tx.ExecContext(ctx, `
			DELETE FROM deployment_files
			WHERE deployment_id IN (SELECT id FROM deployments WHERE project_id = ?)`, id); err != nil {
			return err
		}
		res, err := tx.ExecContext(ctx, `DELETE FROM projects WHERE id = ?`, id)
		if err != nil {
			return mapErr(err)
		}
		n, err := res.RowsAffected()
		if err != nil {
			return err
		}
		if n == 0 {
			return ErrNotFound
		}
		return nil
	})
}

// CountProjects is used by /api/v1/system/info.
func (d *DB) CountProjects(ctx context.Context) (int64, error) {
	var n int64
	if err := d.r.QueryRowContext(ctx, `SELECT count(*) FROM projects`).Scan(&n); err != nil {
		return 0, fmt.Errorf("count projects: %w", err)
	}
	return n, nil
}