package storage

import (
	"database/sql"
	"path/filepath"
	"testing"
)

func TestSQLiteStoreBootstrapsSchema(t *testing.T) {
	dbPath := filepath.Join(t.TempDir(), "app.db")
	store, err := OpenSQLite(dbPath)
	if err != nil {
		t.Fatalf("OpenSQLite: %v", err)
	}
	defer store.Close()

	for _, table := range []string{
		"templates",
		"profiles",
		"overlays",
		"integration_services",
		"video_sources",
		"devices",
		"device_config_state",
		"tasks",
		"task_devices",
		"audit_logs",
	} {
		ok, err := store.HasTable(table)
		if err != nil {
			t.Fatalf("HasTable(%s): %v", table, err)
		}
		if !ok {
			t.Fatalf("expected table %s to exist", table)
		}
	}
}

func TestSQLiteStoreMigratesLegacyProfileTemplateColumn(t *testing.T) {
	dbPath := filepath.Join(t.TempDir(), "app.db")
	legacyDB, err := sql.Open("sqlite", dbPath)
	if err != nil {
		t.Fatalf("open legacy sqlite: %v", err)
	}
	_, err = legacyDB.Exec(`
CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  template_name TEXT NOT NULL DEFAULT '',
  business_name TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
INSERT INTO profiles(name, template_name, business_name, description, body_json, created_at, updated_at)
VALUES('line_a', 'helmet', 'gate', 'desc', '{"name":"line_a","instances":[{"name":"cam1","template":"helmet"}]}', '2026-04-29T00:00:00+08:00', '2026-04-29T00:00:00+08:00');
`)
	if err != nil {
		t.Fatalf("seed legacy schema: %v", err)
	}
	_ = legacyDB.Close()

	store, err := OpenSQLite(dbPath)
	if err != nil {
		t.Fatalf("OpenSQLite migrate legacy: %v", err)
	}
	defer store.Close()

	hasOld, err := hasColumn(store.DB(), "profiles", "template_name")
	if err != nil {
		t.Fatalf("has old column: %v", err)
	}
	if hasOld {
		t.Fatal("expected legacy template_name column to be removed")
	}
	hasNew, err := hasColumn(store.DB(), "profiles", "primary_template_name")
	if err != nil {
		t.Fatalf("has new column: %v", err)
	}
	if !hasNew {
		t.Fatal("expected primary_template_name column to exist after migration")
	}
	repo := NewAssetsRepo(store.DB())
	profile, err := repo.GetProfile("line_a")
	if err != nil {
		t.Fatalf("GetProfile after migration: %v", err)
	}
	if profile == nil || profile.TemplateName != "helmet" {
		t.Fatalf("expected migrated primary template value, got %#v", profile)
	}
}