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)
    }
}
