package storage

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "strings"
    "time"
)

const schema001 = `
CREATE TABLE IF NOT EXISTS templates (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS profiles (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  primary_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
);
CREATE TABLE IF NOT EXISTS overlays (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS integration_services (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  type TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  enabled INTEGER NOT NULL DEFAULT 0,
  body_json TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS video_sources (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  source_type TEXT NOT NULL DEFAULT '',
  area TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS standard_models (
  name TEXT PRIMARY KEY,
  file_name TEXT NOT NULL,
  version TEXT NOT NULL,
  sha256 TEXT NOT NULL,
  size_bytes INTEGER NOT NULL DEFAULT 0,
  model_type TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS standard_resources (
  name TEXT PRIMARY KEY,
  resource_type TEXT NOT NULL DEFAULT '',
  version TEXT NOT NULL DEFAULT '',
  sha256 TEXT NOT NULL DEFAULT '',
  size_bytes INTEGER NOT NULL DEFAULT 0,
  description TEXT NOT NULL DEFAULT '',
  file_path TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS alarm_records (
  id TEXT PRIMARY KEY,
  device_id TEXT NOT NULL,
  channel TEXT NOT NULL,
  timestamp TEXT NOT NULL,
  rule_name TEXT NOT NULL,
  rule_type TEXT NOT NULL,
  object_label TEXT NOT NULL DEFAULT '',
  confidence REAL NOT NULL DEFAULT 0,
  snapshot_url TEXT NOT NULL DEFAULT '',
  clip_url TEXT NOT NULL DEFAULT '',
  duration_ms INTEGER NOT NULL DEFAULT 0,
  collected_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS scene_templates (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  primary_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
);
CREATE TABLE IF NOT EXISTS recognition_units (
  id INTEGER PRIMARY KEY,
  scene_template_name TEXT NOT NULL,
  name TEXT NOT NULL,
  display_name TEXT NOT NULL DEFAULT '',
  site_name TEXT NOT NULL DEFAULT '',
  video_source_ref TEXT NOT NULL DEFAULT '',
  output_channel TEXT NOT NULL DEFAULT '',
  rtsp_port TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  UNIQUE(scene_template_name, name)
);
CREATE TABLE IF NOT EXISTS device_assignments (
  device_id TEXT PRIMARY KEY,
  profile_name TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  body_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS devices (
  device_id TEXT PRIMARY KEY,
  hostname TEXT NOT NULL DEFAULT '',
  ip TEXT NOT NULL DEFAULT '',
  agent_port INTEGER NOT NULL DEFAULT 0,
  media_port INTEGER NOT NULL DEFAULT 0,
  alias TEXT NOT NULL DEFAULT '',
  device_name TEXT NOT NULL DEFAULT '',
  version TEXT NOT NULL DEFAULT '',
  git_sha TEXT NOT NULL DEFAULT '',
  build_id TEXT NOT NULL DEFAULT '',
  last_seen_ms INTEGER NOT NULL DEFAULT 0,
  online INTEGER NOT NULL DEFAULT 0,
  graphs_json TEXT NOT NULL DEFAULT '{}',
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS device_config_state (
  device_id TEXT PRIMARY KEY,
  template_name TEXT NOT NULL DEFAULT '',
  profile_name TEXT NOT NULL DEFAULT '',
  overlays_json TEXT NOT NULL DEFAULT '[]',
  config_id TEXT NOT NULL DEFAULT '',
  config_version TEXT NOT NULL DEFAULT '',
  last_applied_task_id TEXT NOT NULL DEFAULT '',
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS device_features (
  device_id TEXT PRIMARY KEY,
  features_json TEXT NOT NULL DEFAULT '[]',
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS tasks (
  task_id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  payload_json TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL,
  finished_at TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS task_devices (
  task_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  status TEXT NOT NULL,
  progress REAL NOT NULL DEFAULT 0,
  error_text TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (task_id, device_id)
);
CREATE TABLE IF NOT EXISTS audit_logs (
  id INTEGER PRIMARY KEY,
  actor TEXT NOT NULL DEFAULT 'system',
  action TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_id TEXT NOT NULL,
  details_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);
`

func migrate(db *sql.DB) error {
    if _, err := db.Exec(schema001); err != nil {
        return err
    }
    if err := migrateProfilePrimaryTemplateName(db); err != nil {
        return err
    }
    return migrateProfilesToSceneTemplates(db)
}

func migrateProfilePrimaryTemplateName(db *sql.DB) error {
    hasPrimary, err := hasColumn(db, "profiles", "primary_template_name")
    if err != nil {
        return err
    }
    if hasPrimary {
        return nil
    }
    hasLegacy, err := hasColumn(db, "profiles", "template_name")
    if err != nil {
        return err
    }
    if !hasLegacy {
        return fmt.Errorf("profiles table is missing both primary_template_name and template_name")
    }
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() { _ = tx.Rollback() }()
    if _, err := tx.Exec(`ALTER TABLE profiles RENAME TO profiles_legacy_template_name`); err != nil {
        return err
    }
    if _, err := tx.Exec(`
CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  primary_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
)`); err != nil {
        return err
    }
    if _, err := tx.Exec(`
INSERT INTO profiles(id, name, primary_template_name, business_name, description, body_json, created_at, updated_at)
SELECT id, name, template_name, business_name, description, body_json, created_at, updated_at
FROM profiles_legacy_template_name
`); err != nil {
        return err
    }
    if _, err := tx.Exec(`DROP TABLE profiles_legacy_template_name`); err != nil {
        return err
    }
    return tx.Commit()
}

func hasColumn(db *sql.DB, table string, column string) (bool, error) {
    rows, err := db.Query(`PRAGMA table_info(` + table + `)`)
    if err != nil {
        return false, err
    }
    defer rows.Close()
    for rows.Next() {
        var (
            cid       int
            name      string
            ctype     string
            notnull   int
            dfltValue sql.NullString
            pk        int
        )
        if err := rows.Scan(&cid, &name, &ctype, &notnull, &dfltValue, &pk); err != nil {
            return false, err
        }
        if name == column {
            return true, nil
        }
    }
    return false, rows.Err()
}

func migrateProfilesToSceneTemplates(db *sql.DB) error {
    if db == nil {
        return nil
    }
    var count int
    if err := db.QueryRow(`SELECT COUNT(1) FROM scene_templates`).Scan(&count); err != nil {
        return err
    }
    if count > 0 {
        return nil
    }
    rows, err := db.Query(`
SELECT name, primary_template_name, business_name, description, body_json, created_at, updated_at
FROM profiles
ORDER BY created_at ASC, name ASC
`)
    if err != nil {
        return err
    }
    defer rows.Close()

    type legacyProfile struct {
        Name         string
        TemplateName string
        BusinessName string
        Description  string
        BodyJSON     string
        CreatedAt    string
        UpdatedAt    string
    }
    legacy := make([]legacyProfile, 0)
    for rows.Next() {
        var item legacyProfile
        if err := rows.Scan(&item.Name, &item.TemplateName, &item.BusinessName, &item.Description, &item.BodyJSON, &item.CreatedAt, &item.UpdatedAt); err != nil {
            return err
        }
        legacy = append(legacy, item)
    }
    if err := rows.Err(); err != nil {
        return err
    }
    if len(legacy) == 0 {
        return nil
    }

    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer func() { _ = tx.Rollback() }()

    now := time.Now().Format(time.RFC3339)
    for _, item := range legacy {
        templateDoc, units, err := splitLegacyProfileDocument(item)
        if err != nil {
            return err
        }
        templateBody, err := json.MarshalIndent(templateDoc, "", "  ")
        if err != nil {
            return err
        }
        createdAt := firstNonEmpty(item.CreatedAt, now)
        updatedAt := firstNonEmpty(item.UpdatedAt, createdAt)
        if _, err := tx.Exec(`
INSERT INTO scene_templates(name, primary_template_name, business_name, description, body_json, created_at, updated_at)
VALUES(?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(name) DO NOTHING
`, item.Name, item.TemplateName, item.BusinessName, item.Description, string(append(templateBody, '\n')), createdAt, updatedAt); err != nil {
            return err
        }
        for _, unit := range units {
            if _, err := tx.Exec(`
INSERT INTO recognition_units(scene_template_name, name, display_name, site_name, video_source_ref, output_channel, rtsp_port, description, body_json, created_at, updated_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(scene_template_name, name) DO NOTHING
`, item.Name, unit.Name, unit.DisplayName, unit.SiteName, unit.VideoSourceRef, unit.OutputChannel, unit.RTSPPort, item.Description, unit.BodyJSON, createdAt, updatedAt); err != nil {
                return err
            }
        }
    }

    return tx.Commit()
}

type migratedRecognitionUnit struct {
    Name           string
    DisplayName    string
    SiteName       string
    VideoSourceRef string
    OutputChannel  string
    RTSPPort       string
    BodyJSON       string
}

func splitLegacyProfileDocument(item struct {
    Name         string
    TemplateName string
    BusinessName string
    Description  string
    BodyJSON     string
    CreatedAt    string
    UpdatedAt    string
}) (map[string]any, []migratedRecognitionUnit, error) {
    raw := map[string]any{}
    if strings.TrimSpace(item.BodyJSON) != "" {
        if err := json.Unmarshal([]byte(item.BodyJSON), &raw); err != nil {
            return nil, nil, err
        }
    }
    if raw == nil {
        raw = map[string]any{}
    }
    raw["name"] = item.Name
    if strings.TrimSpace(item.TemplateName) != "" {
        raw["primary_template_name"] = item.TemplateName
    }
    if strings.TrimSpace(item.BusinessName) != "" {
        raw["business_name"] = item.BusinessName
    }
    if strings.TrimSpace(item.Description) != "" {
        raw["description"] = item.Description
    }
    var units []migratedRecognitionUnit
    if instances, ok := raw["instances"].([]any); ok {
        for _, entry := range instances {
            inst, _ := entry.(map[string]any)
            if inst == nil {
                continue
            }
            unitName := strings.TrimSpace(stringAny(inst["name"]))
            if unitName == "" {
                continue
            }
            sceneMeta, _ := inst["scene_meta"].(map[string]any)
            inputBindings, _ := inst["input_bindings"].(map[string]any)
            outputBindings, _ := inst["output_bindings"].(map[string]any)
            videoSourceRef := strings.TrimSpace(bindingStringFromAny(inputBindings, "video_input_main", "video_source_ref"))
            outputChannel := strings.TrimSpace(bindingStringFromAny(outputBindings, "stream_output_main", "channel_no"))
            if outputChannel == "" {
                outputChannel = unitName
            }
            rtspPort := strings.TrimSpace(bindingStringFromAny(outputBindings, "stream_output_main", "publish_rtsp_port"))
            if rtspPort == "" {
                rtspPort = "8555"
            }
            unitRaw := cloneAnyMap(inst)
            body, err := json.MarshalIndent(unitRaw, "", "  ")
            if err != nil {
                return nil, nil, err
            }
            units = append(units, migratedRecognitionUnit{
                Name:           unitName,
                DisplayName:    strings.TrimSpace(stringAny(sceneMeta["display_name"])),
                SiteName:       strings.TrimSpace(stringAny(sceneMeta["site_name"])),
                VideoSourceRef: videoSourceRef,
                OutputChannel:  outputChannel,
                RTSPPort:       rtspPort,
                BodyJSON:       string(append(body, '\n')),
            })
        }
    }
    delete(raw, "instances")
    return raw, units, nil
}

func bindingStringFromAny(bindings map[string]any, slot string, field string) string {
    entry, _ := bindings[slot].(map[string]any)
    return strings.TrimSpace(stringAny(entry[field]))
}

func stringAny(v any) string {
    switch vv := v.(type) {
    case string:
        return vv
    case float64:
        return fmt.Sprintf("%v", vv)
    default:
        return ""
    }
}

func cloneAnyMap(in map[string]any) map[string]any {
    if len(in) == 0 {
        return map[string]any{}
    }
    out := make(map[string]any, len(in))
    for k, v := range in {
        out[k] = v
    }
    return out
}

func firstNonEmpty(values ...string) string {
    for _, v := range values {
        if strings.TrimSpace(v) != "" {
            return strings.TrimSpace(v)
        }
    }
    return ""
}
