Stackyard

package storage

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strings"
	"time"

	_ "modernc.org/sqlite"

	"stackyard/internal/project"
)

var collectionColorPattern = regexp.MustCompile(`^#[0-9a-fA-F]{6}$`)

type Store struct {
	db *sql.DB
}

func Open(path string) (*Store, error) {
	if path == "" {
		return nil, nil
	}
	if err := os.MkdirAll(filepath.Dir(path), 0o755); err != nil {
		return nil, fmt.Errorf("create database directory: %w", err)
	}

	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("open sqlite database: %w", err)
	}

	store := &Store{db: db}
	if err := store.init(); err != nil {
		_ = db.Close()
		return nil, err
	}
	return store, nil
}

func (s *Store) Close() error {
	if s == nil || s.db == nil {
		return nil
	}
	return s.db.Close()
}

func (s *Store) init() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS projects (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			root_name TEXT NOT NULL,
			path TEXT NOT NULL UNIQUE,
			primary_type TEXT,
			state TEXT NOT NULL DEFAULT 'active',
			favorite INTEGER NOT NULL DEFAULT 0,
			description TEXT NOT NULL DEFAULT '',
			notes TEXT NOT NULL DEFAULT '',
			last_opened_at TEXT,
			mise_tools_json TEXT NOT NULL DEFAULT '[]',
			ports_json TEXT NOT NULL DEFAULT '[]',
			compose_files_json TEXT NOT NULL DEFAULT '[]',
			todo_path TEXT,
			readme_path TEXT,
			markers_json TEXT NOT NULL DEFAULT '[]',
			last_modified_at TEXT,
			last_scanned_at TEXT,
			created_at TEXT NOT NULL,
			updated_at TEXT NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS git_cache (
			project_id TEXT PRIMARY KEY,
			is_repo INTEGER NOT NULL,
			branch TEXT,
			dirty INTEGER NOT NULL DEFAULT 0,
			ahead INTEGER NOT NULL DEFAULT 0,
			behind INTEGER NOT NULL DEFAULT 0,
			last_commit_hash TEXT,
			last_commit_subject TEXT,
			last_commit_at TEXT,
			updated_at TEXT NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS collections (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL COLLATE NOCASE UNIQUE,
			color TEXT NOT NULL DEFAULT '#38bdf8',
			sort_order INTEGER NOT NULL DEFAULT 0,
			created_at TEXT NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS project_collections (
			project_id TEXT PRIMARY KEY,
			collection_id INTEGER NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS project_labels (
			project_id TEXT NOT NULL,
			label TEXT NOT NULL COLLATE NOCASE,
			PRIMARY KEY (project_id, label)
		);`,
		`CREATE TABLE IF NOT EXISTS snapshots (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL,
			project_ids_json TEXT NOT NULL,
			created_at TEXT NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS scanner_rules (
			path TEXT PRIMARY KEY,
			kind TEXT NOT NULL
		);`,
		`CREATE TABLE IF NOT EXISTS project_dependencies (
			project_id TEXT NOT NULL,
			depends_on_project_id TEXT NOT NULL,
			PRIMARY KEY(project_id, depends_on_project_id)
		);`,
		`CREATE TABLE IF NOT EXISTS events (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			level TEXT NOT NULL,
			message TEXT NOT NULL,
			created_at TEXT NOT NULL
		);`,
	}

	for _, stmt := range stmts {
		if _, err := s.db.Exec(stmt); err != nil {
			return fmt.Errorf("init schema: %w", err)
		}
	}

	migrations := []string{
		`ALTER TABLE projects ADD COLUMN mise_tools_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE projects ADD COLUMN ports_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE projects ADD COLUMN compose_files_json TEXT NOT NULL DEFAULT '[]'`,
		`ALTER TABLE projects ADD COLUMN description TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE projects ADD COLUMN notes TEXT NOT NULL DEFAULT ''`,
		`ALTER TABLE projects ADD COLUMN last_opened_at TEXT`,
		`ALTER TABLE collections ADD COLUMN color TEXT NOT NULL DEFAULT '#38bdf8'`,
		`ALTER TABLE collections ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0`,
	}
	for _, stmt := range migrations {
		if _, err := s.db.Exec(stmt); err != nil && !strings.Contains(err.Error(), "duplicate column name") {
			return fmt.Errorf("migrate schema: %w", err)
		}
	}

	return nil
}

func (s *Store) UpsertProjects(ctx context.Context, projects []project.Project) error {
	if s == nil || s.db == nil {
		return nil
	}

	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback()
		}
	}()

	seen := make([]string, 0, len(projects))
	now := time.Now().UTC().Format(time.RFC3339)
	for _, p := range projects {
		markersJSON, err := json.Marshal(p.Markers)
		if err != nil {
			return fmt.Errorf("marshal markers for %s: %w", p.Path, err)
		}
		miseToolsJSON, err := json.Marshal(p.MiseTools)
		if err != nil {
			return fmt.Errorf("marshal mise tools for %s: %w", p.Path, err)
		}
		portsJSON, err := json.Marshal(p.Ports)
		if err != nil {
			return fmt.Errorf("marshal ports for %s: %w", p.Path, err)
		}
		composeFilesJSON, err := json.Marshal(p.ComposeFiles)
		if err != nil {
			return fmt.Errorf("marshal compose files for %s: %w", p.Path, err)
		}
		lastModified := nullableTime(p.LastModified)
		lastScanned := nullableTime(p.LastScannedAt)

		if _, err := tx.ExecContext(ctx, `
			INSERT INTO projects (
				id, name, root_name, path, primary_type, state, favorite, description, notes, last_opened_at, mise_tools_json, ports_json, compose_files_json, todo_path, readme_path,
				markers_json, last_modified_at, last_scanned_at, created_at, updated_at
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(id) DO UPDATE SET
				name=excluded.name,
				root_name=excluded.root_name,
				path=excluded.path,
				primary_type=excluded.primary_type,
				state=excluded.state,
				favorite=excluded.favorite,
				description=excluded.description,
				notes=excluded.notes,
				last_opened_at=excluded.last_opened_at,
				mise_tools_json=excluded.mise_tools_json,
				ports_json=excluded.ports_json,
				compose_files_json=excluded.compose_files_json,
				todo_path=excluded.todo_path,
				readme_path=excluded.readme_path,
				markers_json=excluded.markers_json,
				last_modified_at=excluded.last_modified_at,
				last_scanned_at=excluded.last_scanned_at,
				updated_at=excluded.updated_at
		`, p.ID, p.Name, p.RootName, p.Path, p.PrimaryType, p.State, boolToInt(p.Favorite), p.Description, p.Notes, nullableTime(p.LastOpenedAt),
			string(miseToolsJSON), string(portsJSON), string(composeFilesJSON), nullIfEmpty(p.TodoPath),
			nullIfEmpty(p.ReadmePath), string(markersJSON), lastModified, lastScanned, now, now); err != nil {
			return fmt.Errorf("upsert project %s: %w", p.Path, err)
		}

		if _, err := tx.ExecContext(ctx, `
			INSERT INTO git_cache (
				project_id, is_repo, branch, dirty, ahead, behind, last_commit_hash, last_commit_subject, last_commit_at, updated_at
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON CONFLICT(project_id) DO UPDATE SET
				is_repo=excluded.is_repo,
				branch=excluded.branch,
				dirty=excluded.dirty,
				ahead=excluded.ahead,
				behind=excluded.behind,
				last_commit_hash=excluded.last_commit_hash,
				last_commit_subject=excluded.last_commit_subject,
				last_commit_at=excluded.last_commit_at,
				updated_at=excluded.updated_at
		`, p.ID, boolToInt(p.Git.IsRepo), nullIfEmpty(p.Git.Branch), boolToInt(p.Git.Dirty), p.Git.Ahead, p.Git.Behind,
			nullIfEmpty(p.Git.LastCommitHash), nullIfEmpty(p.Git.LastCommitSubject), nullableTime(p.Git.LastCommitAt), now); err != nil {
			return fmt.Errorf("upsert git cache %s: %w", p.Path, err)
		}

		seen = append(seen, p.ID)
		if _, err := tx.ExecContext(ctx, `DELETE FROM project_labels WHERE project_id = ?`, p.ID); err != nil {
			return fmt.Errorf("clear labels for %s: %w", p.Path, err)
		}
		for _, label := range p.Labels {
			if _, err := tx.ExecContext(ctx, `INSERT OR IGNORE INTO project_labels (project_id, label) VALUES (?, ?)`, p.ID, label); err != nil {
				return fmt.Errorf("store label for %s: %w", p.Path, err)
			}
		}
	}

	if err := pruneMissing(ctx, tx, seen); err != nil {
		return err
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit tx: %w", err)
	}
	return nil
}

func pruneMissing(ctx context.Context, tx *sql.Tx, ids []string) error {
	if len(ids) == 0 {
		if _, err := tx.ExecContext(ctx, `DELETE FROM project_labels`); err != nil {
			return fmt.Errorf("clear project labels: %w", err)
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM project_collections`); err != nil {
			return fmt.Errorf("clear project collections: %w", err)
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM git_cache`); err != nil {
			return fmt.Errorf("clear git_cache: %w", err)
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM projects`); err != nil {
			return fmt.Errorf("clear projects: %w", err)
		}
		return nil
	}

	query, args := notInClause(`DELETE FROM project_collections WHERE project_id`, ids)
	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("prune project collections: %w", err)
	}
	query, args = notInClause(`DELETE FROM project_labels WHERE project_id`, ids)
	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("prune project labels: %w", err)
	}
	query, args = notInClause(`DELETE FROM git_cache WHERE project_id`, ids)
	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("prune git cache: %w", err)
	}
	query, args = notInClause(`DELETE FROM projects WHERE id`, ids)
	if _, err := tx.ExecContext(ctx, query, args...); err != nil {
		return fmt.Errorf("prune projects: %w", err)
	}
	return nil
}

func (s *Store) ListProjects(ctx context.Context) ([]project.Project, error) {
	if s == nil || s.db == nil {
		return nil, nil
	}

	rows, err := s.db.QueryContext(ctx, `
		SELECT
			p.id, p.name, p.root_name, p.path, p.primary_type, p.state, p.favorite, p.description, p.notes, p.last_opened_at, p.mise_tools_json, p.ports_json, p.compose_files_json, p.todo_path, p.readme_path,
			p.markers_json, p.last_modified_at, p.last_scanned_at,
			g.is_repo, g.branch, g.dirty, g.ahead, g.behind, g.last_commit_hash, g.last_commit_subject, g.last_commit_at
		FROM projects p
		LEFT JOIN git_cache g ON g.project_id = p.id
		ORDER BY p.name, p.path
	`)
	if err != nil {
		return nil, fmt.Errorf("query projects: %w", err)
	}
	defer rows.Close()

	var projects []project.Project
	for rows.Next() {
		var (
			p              project.Project
			state          string
			favorite       int
			miseToolsJSON  string
			portsJSON      string
			composeJSON    string
			markersJSON    string
			todoPath       sql.NullString
			readmePath     sql.NullString
			lastModified   sql.NullString
			lastScanned    sql.NullString
			lastOpened     sql.NullString
			branch         sql.NullString
			lastCommitHash sql.NullString
			lastCommitSubj sql.NullString
			lastCommitAt   sql.NullString
			isRepo         sql.NullInt64
			dirty          sql.NullInt64
			ahead          sql.NullInt64
			behind         sql.NullInt64
		)

		if err := rows.Scan(
			&p.ID, &p.Name, &p.RootName, &p.Path, &p.PrimaryType, &state, &favorite, &p.Description, &p.Notes, &lastOpened, &miseToolsJSON, &portsJSON, &composeJSON, &todoPath, &readmePath,
			&markersJSON, &lastModified, &lastScanned,
			&isRepo, &branch, &dirty, &ahead, &behind, &lastCommitHash, &lastCommitSubj, &lastCommitAt,
		); err != nil {
			return nil, fmt.Errorf("scan project row: %w", err)
		}

		p.State = project.State(state)
		p.Favorite = favorite == 1
		p.TodoPath = todoPath.String
		p.ReadmePath = readmePath.String
		p.LastModified = parseNullableTime(lastModified)
		p.LastScannedAt = parseNullableTime(lastScanned)
		p.LastOpenedAt = parseNullableTime(lastOpened)
		p.Git.IsRepo = isRepo.Valid && isRepo.Int64 == 1
		p.Git.Branch = branch.String
		p.Git.Dirty = dirty.Valid && dirty.Int64 == 1
		if ahead.Valid {
			p.Git.Ahead = int(ahead.Int64)
		}
		if behind.Valid {
			p.Git.Behind = int(behind.Int64)
		}
		p.Git.LastCommitHash = lastCommitHash.String
		p.Git.LastCommitSubject = lastCommitSubj.String
		p.Git.LastCommitAt = parseNullableTime(lastCommitAt)
		if err := json.Unmarshal([]byte(miseToolsJSON), &p.MiseTools); err != nil {
			return nil, fmt.Errorf("decode mise tools for %s: %w", p.Path, err)
		}
		if err := json.Unmarshal([]byte(portsJSON), &p.Ports); err != nil {
			return nil, fmt.Errorf("decode ports for %s: %w", p.Path, err)
		}
		if err := json.Unmarshal([]byte(composeJSON), &p.ComposeFiles); err != nil {
			return nil, fmt.Errorf("decode compose files for %s: %w", p.Path, err)
		}
		if err := json.Unmarshal([]byte(markersJSON), &p.Markers); err != nil {
			return nil, fmt.Errorf("decode markers for %s: %w", p.Path, err)
		}
		projects = append(projects, p)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate projects: %w", err)
	}
	if err := rows.Close(); err != nil {
		return nil, fmt.Errorf("close project rows: %w", err)
	}
	projectIndex := make(map[string]int, len(projects))
	for i := range projects {
		projectIndex[projects[i].ID] = i
	}
	labelRows, err := s.db.QueryContext(ctx, `SELECT project_id, label FROM project_labels ORDER BY label COLLATE NOCASE`)
	if err != nil {
		return nil, fmt.Errorf("query project labels: %w", err)
	}
	defer labelRows.Close()
	for labelRows.Next() {
		var projectID, label string
		if err := labelRows.Scan(&projectID, &label); err != nil {
			return nil, fmt.Errorf("scan project label: %w", err)
		}
		if index, ok := projectIndex[projectID]; ok {
			projects[index].Labels = append(projects[index].Labels, label)
		}
	}
	if err := labelRows.Err(); err != nil {
		return nil, fmt.Errorf("iterate project labels: %w", err)
	}
	return projects, nil
}

func (s *Store) GetProject(ctx context.Context, id string) (project.Project, bool, error) {
	projects, err := s.ListProjects(ctx)
	if err != nil {
		return project.Project{}, false, err
	}
	for _, p := range projects {
		if p.ID == id {
			return p, true, nil
		}
	}
	return project.Project{}, false, nil
}

func (s *Store) SetFavorite(ctx context.Context, id string, favorite bool) error {
	if s == nil || s.db == nil {
		return nil
	}
	_, err := s.db.ExecContext(ctx, `UPDATE projects SET favorite = ?, updated_at = ? WHERE id = ?`, boolToInt(favorite), time.Now().UTC().Format(time.RFC3339), id)
	if err != nil {
		return fmt.Errorf("set favorite: %w", err)
	}
	return nil
}

func (s *Store) SetState(ctx context.Context, id string, state project.State) error {
	if s == nil || s.db == nil {
		return nil
	}
	_, err := s.db.ExecContext(ctx, `UPDATE projects SET state = ?, updated_at = ? WHERE id = ?`, string(state), time.Now().UTC().Format(time.RFC3339), id)
	if err != nil {
		return fmt.Errorf("set state: %w", err)
	}
	return nil
}

func (s *Store) ListCollections(ctx context.Context) ([]project.Collection, error) {
	if s == nil || s.db == nil {
		return nil, nil
	}
	rows, err := s.db.QueryContext(ctx, `
		SELECT c.id, c.name, c.color, c.sort_order, pc.project_id
		FROM collections c
		LEFT JOIN project_collections pc ON pc.collection_id = c.id
		ORDER BY c.sort_order, c.name COLLATE NOCASE, pc.project_id
	`)
	if err != nil {
		return nil, fmt.Errorf("list collections: %w", err)
	}
	defer rows.Close()
	byID := make(map[int64]int)
	collections := make([]project.Collection, 0)
	for rows.Next() {
		var id int64
		var name string
		var color string
		var sortOrder int
		var projectID sql.NullString
		if err := rows.Scan(&id, &name, &color, &sortOrder, &projectID); err != nil {
			return nil, fmt.Errorf("scan collection: %w", err)
		}
		index, ok := byID[id]
		if !ok {
			index = len(collections)
			byID[id] = index
			collections = append(collections, project.Collection{ID: id, Name: name, Color: color, SortOrder: sortOrder})
		}
		if projectID.Valid {
			collections[index].ProjectIDs = append(collections[index].ProjectIDs, projectID.String)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate collections: %w", err)
	}
	return collections, nil
}

func (s *Store) CreateCollection(ctx context.Context, name string) error {
	if s == nil || s.db == nil {
		return errors.New("storage unavailable")
	}
	name = strings.TrimSpace(name)
	if name == "" {
		return errors.New("collection name is empty")
	}
	if len([]rune(name)) > 80 {
		return errors.New("collection name is too long")
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO collections (name, sort_order, created_at) SELECT ?, COALESCE(MAX(sort_order), -1) + 1, ? FROM collections`, name, time.Now().UTC().Format(time.RFC3339))
	if err != nil {
		return fmt.Errorf("create collection: %w", err)
	}
	return nil
}

func (s *Store) UpdateCollection(ctx context.Context, id int64, name, color string) error {
	if s == nil || s.db == nil {
		return errors.New("storage unavailable")
	}
	name = strings.TrimSpace(name)
	if name == "" || len([]rune(name)) > 80 {
		return errors.New("invalid collection name")
	}
	if !collectionColorPattern.MatchString(color) {
		return errors.New("invalid collection color")
	}
	result, err := s.db.ExecContext(ctx, `UPDATE collections SET name = ?, color = ? WHERE id = ?`, name, color, id)
	if err != nil {
		return fmt.Errorf("update collection: %w", err)
	}
	changed, _ := result.RowsAffected()
	if changed == 0 {
		return errors.New("collection not found")
	}
	return nil
}

func (s *Store) MoveCollection(ctx context.Context, id int64, direction int) error {
	collections, err := s.ListCollections(ctx)
	if err != nil {
		return err
	}
	index := -1
	for i := range collections {
		if collections[i].ID == id {
			index = i
			break
		}
	}
	target := index + direction
	if index < 0 || target < 0 || target >= len(collections) {
		return nil
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `UPDATE collections SET sort_order = ? WHERE id = ?`, collections[target].SortOrder, collections[index].ID); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `UPDATE collections SET sort_order = ? WHERE id = ?`, collections[index].SortOrder, collections[target].ID); err != nil {
		return err
	}
	return tx.Commit()
}

func (s *Store) SetProjectMetadata(ctx context.Context, id, description, notes, labelsInput string) error {
	if s == nil || s.db == nil {
		return errors.New("storage unavailable")
	}
	if len([]rune(description)) > 500 || len([]rune(notes)) > 5000 {
		return errors.New("project metadata is too long")
	}
	labels := normalizeLabels(labelsInput)
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	result, err := tx.ExecContext(ctx, `UPDATE projects SET description = ?, notes = ?, updated_at = ? WHERE id = ?`, strings.TrimSpace(description), strings.TrimSpace(notes), time.Now().UTC().Format(time.RFC3339), id)
	if err != nil {
		return fmt.Errorf("update project metadata: %w", err)
	}
	changed, _ := result.RowsAffected()
	if changed == 0 {
		return errors.New("project not found")
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM project_labels WHERE project_id = ?`, id); err != nil {
		return err
	}
	for _, label := range labels {
		if _, err := tx.ExecContext(ctx, `INSERT INTO project_labels (project_id, label) VALUES (?, ?)`, id, label); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *Store) TouchProjectOpened(ctx context.Context, id string) error {
	if s == nil || s.db == nil {
		return nil
	}
	_, err := s.db.ExecContext(ctx, `UPDATE projects SET last_opened_at = ?, updated_at = ? WHERE id = ?`, time.Now().UTC().Format(time.RFC3339), time.Now().UTC().Format(time.RFC3339), id)
	return err
}

func normalizeLabels(input string) []string {
	seen := make(map[string]struct{})
	labels := make([]string, 0)
	for _, raw := range strings.Split(input, ",") {
		label := strings.TrimSpace(raw)
		if label == "" || len([]rune(label)) > 32 {
			continue
		}
		key := strings.ToLower(label)
		if _, ok := seen[key]; ok || len(labels) >= 12 {
			continue
		}
		seen[key] = struct{}{}
		labels = append(labels, label)
	}
	return labels
}

func (s *Store) CreateSnapshot(ctx context.Context, name string, projectIDs []string) error {
	name = strings.TrimSpace(name)
	if name == "" || len([]rune(name)) > 80 {
		return errors.New("invalid snapshot name")
	}
	data, err := json.Marshal(projectIDs)
	if err != nil {
		return err
	}
	_, err = s.db.ExecContext(ctx, `INSERT INTO snapshots (name, project_ids_json, created_at) VALUES (?, ?, ?)`, name, data, time.Now().UTC().Format(time.RFC3339))
	return err
}

func (s *Store) ListSnapshots(ctx context.Context) ([]project.Snapshot, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, name, project_ids_json, created_at FROM snapshots ORDER BY created_at DESC`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var values []project.Snapshot
	for rows.Next() {
		var value project.Snapshot
		var data, created string
		if err := rows.Scan(&value.ID, &value.Name, &data, &created); err != nil {
			return nil, err
		}
		_ = json.Unmarshal([]byte(data), &value.ProjectIDs)
		value.CreatedAt, _ = time.Parse(time.RFC3339, created)
		values = append(values, value)
	}
	return values, rows.Err()
}

func (s *Store) DeleteSnapshot(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM snapshots WHERE id = ?`, id)
	return err
}

func (s *Store) GetSnapshot(ctx context.Context, id int64) (project.Snapshot, bool, error) {
	values, err := s.ListSnapshots(ctx)
	if err != nil {
		return project.Snapshot{}, false, err
	}
	for _, value := range values {
		if value.ID == id {
			return value, true, nil
		}
	}
	return project.Snapshot{}, false, nil
}

func (s *Store) SetScannerRule(ctx context.Context, path, kind string) error {
	path = filepath.Clean(strings.TrimSpace(path))
	if path == "." || (kind != "manual" && kind != "ignore") {
		return errors.New("invalid scanner rule")
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO scanner_rules(path, kind) VALUES(?, ?) ON CONFLICT(path) DO UPDATE SET kind=excluded.kind`, path, kind)
	return err
}

func (s *Store) DeleteScannerRule(ctx context.Context, path string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM scanner_rules WHERE path = ?`, filepath.Clean(path))
	return err
}

func (s *Store) ListScannerRules(ctx context.Context) ([]project.ScannerRule, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT path, kind FROM scanner_rules ORDER BY kind, path`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var values []project.ScannerRule
	for rows.Next() {
		var value project.ScannerRule
		if err := rows.Scan(&value.Path, &value.Kind); err != nil {
			return nil, err
		}
		values = append(values, value)
	}
	return values, rows.Err()
}

func (s *Store) SetDependency(ctx context.Context, projectID, dependsOn string) error {
	if projectID == "" || dependsOn == "" || projectID == dependsOn {
		return errors.New("invalid project dependency")
	}
	_, err := s.db.ExecContext(ctx, `INSERT OR IGNORE INTO project_dependencies(project_id, depends_on_project_id) VALUES(?, ?)`, projectID, dependsOn)
	return err
}

func (s *Store) DeleteDependency(ctx context.Context, projectID, dependsOn string) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM project_dependencies WHERE project_id=? AND depends_on_project_id=?`, projectID, dependsOn)
	return err
}

func (s *Store) ListDependencies(ctx context.Context) ([]project.Dependency, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT project_id, depends_on_project_id FROM project_dependencies ORDER BY project_id, depends_on_project_id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var values []project.Dependency
	for rows.Next() {
		var value project.Dependency
		if err := rows.Scan(&value.ProjectID, &value.DependsOnProjectID); err != nil {
			return nil, err
		}
		values = append(values, value)
	}
	return values, rows.Err()
}

func (s *Store) AddEvent(ctx context.Context, level, message string) error {
	if level != "info" && level != "warning" && level != "error" {
		level = "info"
	}
	if len([]rune(message)) > 1000 {
		message = string([]rune(message)[:1000])
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO events(level,message,created_at) VALUES(?,?,?)`, level, message, time.Now().UTC().Format(time.RFC3339))
	return err
}

func (s *Store) ListEvents(ctx context.Context, limit int) ([]project.Event, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	rows, err := s.db.QueryContext(ctx, `SELECT id,level,message,created_at FROM events ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var values []project.Event
	for rows.Next() {
		var value project.Event
		var created string
		if err := rows.Scan(&value.ID, &value.Level, &value.Message, &created); err != nil {
			return nil, err
		}
		value.CreatedAt, _ = time.Parse(time.RFC3339, created)
		values = append(values, value)
	}
	return values, rows.Err()
}

func (s *Store) DeleteCollection(ctx context.Context, id int64) error {
	if s == nil || s.db == nil {
		return errors.New("storage unavailable")
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin delete collection: %w", err)
	}
	defer tx.Rollback()
	if _, err := tx.ExecContext(ctx, `DELETE FROM project_collections WHERE collection_id = ?`, id); err != nil {
		return fmt.Errorf("clear collection assignments: %w", err)
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM collections WHERE id = ?`, id); err != nil {
		return fmt.Errorf("delete collection: %w", err)
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit delete collection: %w", err)
	}
	return nil
}

func (s *Store) AssignProjectCollection(ctx context.Context, projectID string, collectionID int64) error {
	if s == nil || s.db == nil {
		return errors.New("storage unavailable")
	}
	if strings.TrimSpace(projectID) == "" {
		return errors.New("project id is empty")
	}
	if collectionID == 0 {
		_, err := s.db.ExecContext(ctx, `DELETE FROM project_collections WHERE project_id = ?`, projectID)
		return err
	}
	var exists int
	if err := s.db.QueryRowContext(ctx, `SELECT 1 FROM collections WHERE id = ?`, collectionID).Scan(&exists); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return errors.New("collection not found")
		}
		return fmt.Errorf("check collection: %w", err)
	}
	_, err := s.db.ExecContext(ctx, `
		INSERT INTO project_collections (project_id, collection_id) VALUES (?, ?)
		ON CONFLICT(project_id) DO UPDATE SET collection_id = excluded.collection_id
	`, projectID, collectionID)
	if err != nil {
		return fmt.Errorf("assign project collection: %w", err)
	}
	return nil
}

func nullableTime(ts time.Time) any {
	if ts.IsZero() {
		return nil
	}
	return ts.UTC().Format(time.RFC3339)
}

func parseNullableTime(value sql.NullString) time.Time {
	if !value.Valid || value.String == "" {
		return time.Time{}
	}
	ts, err := time.Parse(time.RFC3339, value.String)
	if err != nil {
		return time.Time{}
	}
	return ts
}

func boolToInt(v bool) int {
	if v {
		return 1
	}
	return 0
}

func nullIfEmpty(v string) any {
	if v == "" {
		return nil
	}
	return v
}

func notInClause(prefix string, ids []string) (string, []any) {
	placeholders := make([]byte, 0, len(ids)*2)
	args := make([]any, 0, len(ids))
	for i, id := range ids {
		if i > 0 {
			placeholders = append(placeholders, ',', ' ')
		}
		placeholders = append(placeholders, '?')
		args = append(args, id)
	}
	return fmt.Sprintf("%s NOT IN (%s)", prefix, string(placeholders)), args
}