db_embedded_migrations.h

/**
 * @file db_embedded_migrations.h
 * @brief Auto-generated embedded SQL migrations.
 * DO NOT EDIT - generated by scripts/generate_embedded_migrations.py
 */

#ifndef DB_EMBEDDED_MIGRATIONS_H
#define DB_EMBEDDED_MIGRATIONS_H

#include "database/sqlite_migrate.h"

static const char migration_0001_up[] =
    "-- Streams configuration table\n"
    "CREATE TABLE IF NOT EXISTS streams (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    name TEXT NOT NULL UNIQUE,\n"
    "    url TEXT NOT NULL,\n"
    "    enabled INTEGER DEFAULT 1,\n"
    "    streaming_enabled INTEGER DEFAULT 1,\n"
    "    width INTEGER DEFAULT 1280,\n"
    "    height INTEGER DEFAULT 720,\n"
    "    fps INTEGER DEFAULT 30,\n"
    "    codec TEXT DEFAULT 'h264',\n"
    "    priority INTEGER DEFAULT 5,\n"
    "    record INTEGER DEFAULT 1,\n"
    "    segment_duration INTEGER DEFAULT 900\n"
    ");\n"
    "\n"
    "-- Recordings table\n"
    "CREATE TABLE IF NOT EXISTS recordings (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    file_path TEXT NOT NULL,\n"
    "    start_time INTEGER NOT NULL,\n"
    "    end_time INTEGER,\n"
    "    size_bytes INTEGER DEFAULT 0,\n"
    "    width INTEGER,\n"
    "    height INTEGER,\n"
    "    fps INTEGER,\n"
    "    codec TEXT,\n"
    "    is_complete INTEGER DEFAULT 0,\n"
    "    created_at INTEGER DEFAULT (strftime('%s', 'now'))\n"
    ");\n"
    "\n"
    "-- Events table\n"
    "CREATE TABLE IF NOT EXISTS events (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    type INTEGER NOT NULL,\n"
    "    timestamp INTEGER NOT NULL,\n"
    "    stream_name TEXT,\n"
    "    description TEXT NOT NULL,\n"
    "    details TEXT\n"
    ");\n"
    "\n"
    "-- Create index for efficient queries\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_stream_name ON recordings(stream_name);\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_start_time ON recordings(start_time);";

static const char migration_0001_down[] =
    "DROP TABLE IF EXISTS events;\n"
    "DROP INDEX IF EXISTS idx_recordings_start_time;\n"
    "DROP INDEX IF EXISTS idx_recordings_stream_name;\n"
    "DROP TABLE IF EXISTS recordings;\n"
    "DROP TABLE IF EXISTS streams;";

static const char migration_0002_up[] =
    "ALTER TABLE streams ADD COLUMN detection_based_recording INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN detection_model TEXT DEFAULT '';\n"
    "ALTER TABLE streams ADD COLUMN detection_threshold REAL DEFAULT 0.5;\n"
    "ALTER TABLE streams ADD COLUMN detection_interval INTEGER DEFAULT 10;\n"
    "ALTER TABLE streams ADD COLUMN pre_detection_buffer INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN post_detection_buffer INTEGER DEFAULT 3;";

static const char migration_0002_down[] =
    "-- SQLite doesn't support DROP COLUMN before 3.35.0\n"
    "-- For older versions, we'd need to recreate the table\n"
    "-- This is a no-op for safety on older SQLite versions\n"
    "SELECT 1;";

static const char migration_0003_up[] =
    "ALTER TABLE streams ADD COLUMN protocol INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN is_onvif INTEGER DEFAULT 0;";

static const char migration_0003_down[] =
    "SELECT 1;";

static const char migration_0004_up[] =
    "ALTER TABLE streams ADD COLUMN record_audio INTEGER DEFAULT 1;";

static const char migration_0004_down[] =
    "SELECT 1;";

static const char migration_0005_up[] =
    "CREATE TABLE IF NOT EXISTS detections (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    timestamp INTEGER NOT NULL,\n"
    "    label TEXT NOT NULL,\n"
    "    confidence REAL NOT NULL,\n"
    "    x REAL,\n"
    "    y REAL,\n"
    "    width REAL,\n"
    "    height REAL,\n"
    "    recording_id INTEGER,\n"
    "    created_at INTEGER DEFAULT (strftime('%s', 'now')),\n"
    "    FOREIGN KEY (recording_id) REFERENCES recordings(id)\n"
    ");\n"
    "\n"
    "CREATE INDEX IF NOT EXISTS idx_detections_stream ON detections(stream_name);\n"
    "CREATE INDEX IF NOT EXISTS idx_detections_timestamp ON detections(timestamp);\n"
    "CREATE INDEX IF NOT EXISTS idx_detections_label ON detections(label);";

static const char migration_0005_down[] =
    "DROP INDEX IF EXISTS idx_detections_label;\n"
    "DROP INDEX IF EXISTS idx_detections_timestamp;\n"
    "DROP INDEX IF EXISTS idx_detections_stream;\n"
    "DROP TABLE IF EXISTS detections;";

static const char migration_0006_up[] =
    "CREATE TABLE IF NOT EXISTS users (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    username TEXT NOT NULL UNIQUE,\n"
    "    password_hash TEXT NOT NULL,\n"
    "    salt TEXT NOT NULL,\n"
    "    role TEXT NOT NULL,\n"
    "    email TEXT,\n"
    "    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    last_login INTEGER,\n"
    "    is_active INTEGER DEFAULT 1,\n"
    "    api_key TEXT\n"
    ");\n"
    "\n"
    "-- Create indexes for faster lookups\n"
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users (username);\n"
    "CREATE INDEX IF NOT EXISTS idx_users_api_key ON users (api_key);\n"
    "\n"
    "-- Create sessions table for token-based authentication\n"
    "CREATE TABLE IF NOT EXISTS sessions (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    user_id INTEGER NOT NULL,\n"
    "    token TEXT NOT NULL UNIQUE,\n"
    "    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    expires_at INTEGER NOT NULL,\n"
    "    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions (token);\n"
    "CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions (user_id);";

static const char migration_0006_down[] =
    "DROP TABLE IF EXISTS sessions;\n"
    "DROP TABLE IF EXISTS users;";

static const char migration_0007_up[] =
    "CREATE TABLE IF NOT EXISTS motion_settings (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    stream_name TEXT NOT NULL UNIQUE,\n"
    "    enabled INTEGER DEFAULT 0,\n"
    "    sensitivity REAL DEFAULT 0.5,\n"
    "    threshold REAL DEFAULT 0.01,\n"
    "    cooldown_seconds INTEGER DEFAULT 5,\n"
    "    min_motion_frames INTEGER DEFAULT 3,\n"
    "    created_at INTEGER DEFAULT (strftime('%s', 'now')),\n"
    "    updated_at INTEGER DEFAULT (strftime('%s', 'now'))\n"
    ");";

static const char migration_0007_down[] =
    "DROP TABLE IF EXISTS motion_settings;";

static const char migration_0008_up[] =
    "CREATE TABLE IF NOT EXISTS detection_zones (\n"
    "    id TEXT PRIMARY KEY,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    name TEXT NOT NULL,\n"
    "    enabled INTEGER DEFAULT 1,\n"
    "    color TEXT DEFAULT '#3b82f6',\n"
    "    polygon TEXT NOT NULL,\n"
    "    filter_classes TEXT DEFAULT '',\n"
    "    min_confidence REAL DEFAULT 0.0,\n"
    "    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    FOREIGN KEY (stream_name) REFERENCES streams(name) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "CREATE INDEX IF NOT EXISTS idx_zones_stream ON detection_zones(stream_name);";

static const char migration_0008_down[] =
    "DROP INDEX IF EXISTS idx_zones_stream;\n"
    "DROP TABLE IF EXISTS detection_zones;";

static const char migration_0009_up[] =
    "ALTER TABLE detections ADD COLUMN track_id INTEGER DEFAULT -1;\n"
    "ALTER TABLE detections ADD COLUMN zone_id TEXT DEFAULT '';";

static const char migration_0009_down[] =
    "SELECT 1;";

static const char migration_0010_up[] =
    "ALTER TABLE recordings ADD COLUMN trigger_type TEXT DEFAULT 'scheduled';";

static const char migration_0010_down[] =
    "SELECT 1;";

static const char migration_0011_up[] =
    "ALTER TABLE streams ADD COLUMN detection_api_url TEXT DEFAULT '';";

static const char migration_0011_down[] =
    "SELECT 1;";

static const char migration_0012_up[] =
    "ALTER TABLE streams ADD COLUMN backchannel_enabled INTEGER DEFAULT 0;";

static const char migration_0012_down[] =
    "SELECT 1;";

static const char migration_0013_up[] =
    "-- Per-stream retention settings\n"
    "ALTER TABLE streams ADD COLUMN retention_days INTEGER DEFAULT 30;\n"
    "ALTER TABLE streams ADD COLUMN detection_retention_days INTEGER DEFAULT 90;\n"
    "ALTER TABLE streams ADD COLUMN max_storage_mb INTEGER DEFAULT 0;\n"
    "\n"
    "-- Recording protection\n"
    "ALTER TABLE recordings ADD COLUMN protected INTEGER DEFAULT 0;\n"
    "ALTER TABLE recordings ADD COLUMN retention_override_days INTEGER DEFAULT NULL;\n"
    "\n"
    "-- Indexes for efficient retention queries\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_protected ON recordings(protected);\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_trigger_type ON recordings(trigger_type);";

static const char migration_0013_down[] =
    "DROP INDEX IF EXISTS idx_recordings_trigger_type;\n"
    "DROP INDEX IF EXISTS idx_recordings_protected;\n"
    "SELECT 1;";

static const char migration_0014_up[] =
    "ALTER TABLE streams ADD COLUMN ptz_enabled INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN ptz_max_x INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN ptz_max_y INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN ptz_max_z INTEGER DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN ptz_has_home INTEGER DEFAULT 0;";

static const char migration_0014_down[] =
    "SELECT 1;";

static const char migration_0015_up[] =
    "-- Buffer strategy: 'auto', 'none', 'go2rtc', 'hls_segment', 'memory_packet', 'mmap_hybrid'\n"
    "ALTER TABLE streams ADD COLUMN buffer_strategy TEXT DEFAULT 'auto';";

static const char migration_0015_down[] =
    "SELECT 1;";

static const char migration_0016_up[] =
    "ALTER TABLE streams ADD COLUMN onvif_username TEXT DEFAULT '';\n"
    "ALTER TABLE streams ADD COLUMN onvif_password TEXT DEFAULT '';\n"
    "ALTER TABLE streams ADD COLUMN onvif_profile TEXT DEFAULT '';";

static const char migration_0016_down[] =
    "SELECT 1;";

static const char migration_0017_up[] =
    "ALTER TABLE detections ADD COLUMN recording_id INTEGER REFERENCES recordings(id);";

static const char migration_0017_down[] =
    "SELECT 1;";

static const char migration_0018_up[] =
    "-- Add ip_address column for tracking client IP\n"
    "ALTER TABLE sessions ADD COLUMN ip_address TEXT;\n"
    "\n"
    "-- Add user_agent column for tracking client user agent\n"
    "ALTER TABLE sessions ADD COLUMN user_agent TEXT;";

static const char migration_0018_down[] =
    "-- SQLite doesn't support DROP COLUMN directly, so we need to recreate the table\n"
    "-- For simplicity in the down migration, we'll just leave the columns\n"
    "-- (they're nullable, so this won't break anything)";

static const char migration_0019_up[] =
    "-- Add password_change_locked column to users table\n"
    "-- This allows admins to lock password changes for specific users (e.g., demo accounts)\n"
    "ALTER TABLE users ADD COLUMN password_change_locked INTEGER DEFAULT 0;";

static const char migration_0019_down[] =
    "-- SQLite doesn't support DROP COLUMN directly, so we need to recreate the table\n"
    "-- For simplicity in the down migration, we'll just leave the column\n"
    "-- (it's nullable with a default, so this won't break anything)";

static const char migration_0020_up[] =
    "-- Motion recording configuration table (per-stream settings)\n"
    "CREATE TABLE IF NOT EXISTS motion_recording_config (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    stream_name TEXT NOT NULL UNIQUE,\n"
    "    enabled INTEGER DEFAULT 1,\n"
    "    pre_buffer_seconds INTEGER DEFAULT 5,\n"
    "    post_buffer_seconds INTEGER DEFAULT 5,\n"
    "    max_file_duration INTEGER DEFAULT 300,\n"
    "    codec TEXT DEFAULT 'h264',\n"
    "    quality TEXT DEFAULT 'medium',\n"
    "    retention_days INTEGER DEFAULT 7,\n"
    "    max_storage_mb INTEGER DEFAULT 0,\n"
    "    created_at INTEGER NOT NULL,\n"
    "    updated_at INTEGER NOT NULL,\n"
    "    FOREIGN KEY (stream_name) REFERENCES streams(name) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "-- Index for faster lookups\n"
    "CREATE INDEX IF NOT EXISTS idx_motion_config_stream ON motion_recording_config(stream_name);\n"
    "\n"
    "-- Motion recordings table (separate from regular recordings)\n"
    "CREATE TABLE IF NOT EXISTS motion_recordings (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    file_path TEXT NOT NULL UNIQUE,\n"
    "    start_time INTEGER NOT NULL,\n"
    "    end_time INTEGER,\n"
    "    size_bytes INTEGER DEFAULT 0,\n"
    "    width INTEGER,\n"
    "    height INTEGER,\n"
    "    fps INTEGER,\n"
    "    codec TEXT,\n"
    "    is_complete INTEGER DEFAULT 0,\n"
    "    created_at INTEGER NOT NULL,\n"
    "    FOREIGN KEY (stream_name) REFERENCES streams(name) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "-- Indexes for faster queries\n"
    "CREATE INDEX IF NOT EXISTS idx_motion_recordings_stream ON motion_recordings(stream_name);\n"
    "CREATE INDEX IF NOT EXISTS idx_motion_recordings_time ON motion_recordings(start_time);\n"
    "CREATE INDEX IF NOT EXISTS idx_motion_recordings_complete ON motion_recordings(is_complete);";

static const char migration_0020_down[] =
    "DROP INDEX IF EXISTS idx_motion_recordings_complete;\n"
    "DROP INDEX IF EXISTS idx_motion_recordings_time;\n"
    "DROP INDEX IF EXISTS idx_motion_recordings_stream;\n"
    "DROP TABLE IF EXISTS motion_recordings;\n"
    "DROP INDEX IF EXISTS idx_motion_config_stream;\n"
    "DROP TABLE IF EXISTS motion_recording_config;";

static const char migration_0021_up[] =
    "ALTER TABLE users ADD COLUMN totp_secret TEXT;\n"
    "ALTER TABLE users ADD COLUMN totp_enabled INTEGER DEFAULT 0;";

static const char migration_0021_down[] =
    "-- SQLite doesn't support DROP COLUMN in older versions\n"
    "-- The columns are nullable with safe defaults, so they can remain";

static const char migration_0022_up[] =
    "DROP TABLE IF EXISTS zones;\n"
    "\n"
    "CREATE TABLE IF NOT EXISTS detection_zones (\n"
    "    id TEXT PRIMARY KEY,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    name TEXT NOT NULL,\n"
    "    enabled INTEGER DEFAULT 1,\n"
    "    color TEXT DEFAULT '#3b82f6',\n"
    "    polygon TEXT NOT NULL,\n"
    "    filter_classes TEXT DEFAULT '',\n"
    "    min_confidence REAL DEFAULT 0.0,\n"
    "    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    FOREIGN KEY (stream_name) REFERENCES streams(name) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "CREATE INDEX IF NOT EXISTS idx_detection_zones_stream ON detection_zones(stream_name);";

static const char migration_0022_down[] =
    "DROP INDEX IF EXISTS idx_detection_zones_stream;\n"
    "DROP TABLE IF EXISTS detection_zones;";

static const char migration_0023_up[] =
    "ALTER TABLE streams ADD COLUMN detection_object_filter TEXT DEFAULT 'none';\n"
    "ALTER TABLE streams ADD COLUMN detection_object_filter_list TEXT DEFAULT '';";

static const char migration_0023_down[] =
    "-- SQLite doesn't support DROP COLUMN in older versions\n"
    "-- These columns will just be ignored if not used";

static const char migration_0024_up[] =
    "-- Tiered retention columns on recordings\n"
    "ALTER TABLE recordings ADD COLUMN retention_tier INTEGER DEFAULT 2;\n"
    "ALTER TABLE recordings ADD COLUMN disk_pressure_eligible INTEGER DEFAULT 1;\n"
    "\n"
    "-- Tier multipliers and storage priority on streams\n"
    "ALTER TABLE streams ADD COLUMN tier_critical_multiplier REAL DEFAULT 3.0;\n"
    "ALTER TABLE streams ADD COLUMN tier_important_multiplier REAL DEFAULT 2.0;\n"
    "ALTER TABLE streams ADD COLUMN tier_ephemeral_multiplier REAL DEFAULT 0.25;\n"
    "ALTER TABLE streams ADD COLUMN storage_priority INTEGER DEFAULT 5;\n"
    "\n"
    "-- Daily storage statistics table for analytics\n"
    "CREATE TABLE IF NOT EXISTS storage_daily_stats (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    date TEXT NOT NULL,\n"
    "    stream_name TEXT NOT NULL,\n"
    "    tier INTEGER NOT NULL DEFAULT 2,\n"
    "    recording_count INTEGER NOT NULL DEFAULT 0,\n"
    "    total_bytes INTEGER NOT NULL DEFAULT 0,\n"
    "    deleted_count INTEGER NOT NULL DEFAULT 0,\n"
    "    deleted_bytes INTEGER NOT NULL DEFAULT 0,\n"
    "    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n"
    ");\n"
    "\n"
    "-- Indexes for efficient queries on daily stats\n"
    "CREATE INDEX IF NOT EXISTS idx_storage_daily_stats_stream_date ON storage_daily_stats(stream_name, date);\n"
    "CREATE INDEX IF NOT EXISTS idx_storage_daily_stats_date_tier ON storage_daily_stats(date, tier);\n"
    "\n"
    "-- Index for tier-based retention queries\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_retention_tier ON recordings(retention_tier);\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_disk_pressure ON recordings(disk_pressure_eligible);";

static const char migration_0024_down[] =
    "DROP INDEX IF EXISTS idx_recordings_disk_pressure;\n"
    "DROP INDEX IF EXISTS idx_recordings_retention_tier;\n"
    "DROP INDEX IF EXISTS idx_storage_daily_stats_date_tier;\n"
    "DROP INDEX IF EXISTS idx_storage_daily_stats_stream_date;\n"
    "DROP TABLE IF EXISTS storage_daily_stats;\n"
    "SELECT 1;";

static const char migration_0025_up[] =
    "-- Detection performance indexes (new)\n"
    "CREATE INDEX IF NOT EXISTS idx_detections_recording_id ON detections(recording_id);\n"
    "\n"
    "-- Indexes previously only in db_core.c (never migrated)\n"
    "CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);\n"
    "CREATE INDEX IF NOT EXISTS idx_events_type ON events(type);\n"
    "CREATE INDEX IF NOT EXISTS idx_events_stream ON events(stream_name);\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_end_time ON recordings(end_time);\n"
    "CREATE INDEX IF NOT EXISTS idx_recordings_complete_stream_start ON recordings(is_complete, stream_name, start_time);\n"
    "CREATE INDEX IF NOT EXISTS idx_streams_name ON streams(name);\n"
    "CREATE INDEX IF NOT EXISTS idx_detections_stream_timestamp ON detections(stream_name, timestamp);";

static const char migration_0025_down[] =
    "DROP INDEX IF EXISTS idx_detections_stream_timestamp;\n"
    "DROP INDEX IF EXISTS idx_streams_name;\n"
    "DROP INDEX IF EXISTS idx_recordings_complete_stream_start;\n"
    "DROP INDEX IF EXISTS idx_recordings_end_time;\n"
    "DROP INDEX IF EXISTS idx_events_stream;\n"
    "DROP INDEX IF EXISTS idx_events_type;\n"
    "DROP INDEX IF EXISTS idx_events_timestamp;\n"
    "DROP INDEX IF EXISTS idx_detections_recording_id;";

static const char migration_0026_up[] =
    "ALTER TABLE streams ADD COLUMN record_on_schedule INTEGER NOT NULL DEFAULT 0;\n"
    "ALTER TABLE streams ADD COLUMN recording_schedule TEXT DEFAULT NULL;";

static const char migration_0026_down[] =
    "SELECT 1;";

static const char migration_0027_up[] =
    "ALTER TABLE streams ADD COLUMN group_name TEXT NOT NULL DEFAULT '';";

static const char migration_0027_down[] =
    "SELECT 1;";

static const char migration_0028_up[] =
    "ALTER TABLE streams ADD COLUMN tags TEXT NOT NULL DEFAULT '';"
    "UPDATE streams SET tags = group_name WHERE (tags = '' OR tags IS NULL) AND group_name IS NOT NULL AND group_name != '';"
    "ALTER TABLE streams DROP COLUMN group_name;";

static const char migration_0028_down[] =
    "ALTER TABLE streams ADD COLUMN group_name TEXT NOT NULL DEFAULT '';"
    "UPDATE streams SET group_name = CASE WHEN instr(tags, ',') > 0 THEN substr(tags, 1, instr(tags, ',') - 1) ELSE tags END WHERE tags != '';"
    "ALTER TABLE streams DROP COLUMN tags;";

static const char migration_0029_up[] =
    "ALTER TABLE users ADD COLUMN allowed_tags TEXT DEFAULT NULL;";

static const char migration_0029_down[] =
    "SELECT 1;";

static const char migration_0030_up[] =
    "CREATE TABLE IF NOT EXISTS system_settings (\n"
    "    key   TEXT PRIMARY KEY,\n"
    "    value TEXT NOT NULL DEFAULT '',\n"
    "    updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))\n"
    ");\n"
    "INSERT OR IGNORE INTO system_settings (key, value) VALUES ('setup_complete', '0');";

static const char migration_0030_down[] =
    "DROP TABLE IF EXISTS system_settings;";

static const char migration_0031_up[] =
    "ALTER TABLE streams ADD COLUMN onvif_port INTEGER DEFAULT 0;";

static const char migration_0031_down[] =
    "SELECT 1;";

static const char migration_0032_up[] =
    "CREATE TABLE IF NOT EXISTS recording_tags (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    recording_id INTEGER NOT NULL,\n"
    "    tag TEXT NOT NULL,\n"
    "    created_at INTEGER DEFAULT (strftime('%s', 'now')),\n"
    "    FOREIGN KEY (recording_id) REFERENCES recordings(id) ON DELETE CASCADE\n"
    ");\n"
    "\n"
    "CREATE UNIQUE INDEX IF NOT EXISTS idx_recording_tags_unique ON recording_tags(recording_id, tag);\n"
    "CREATE INDEX IF NOT EXISTS idx_recording_tags_tag ON recording_tags(tag);\n"
    "CREATE INDEX IF NOT EXISTS idx_recording_tags_recording ON recording_tags(recording_id);";

static const char migration_0032_down[] =
    "DROP INDEX IF EXISTS idx_recording_tags_recording;\n"
    "DROP INDEX IF EXISTS idx_recording_tags_tag;\n"
    "DROP INDEX IF EXISTS idx_recording_tags_unique;\n"
    "DROP TABLE IF EXISTS recording_tags;";

static const char migration_0033_up[] =
    "DELETE FROM streams WHERE TRIM(name) = '' OR name IS NULL;";

static const char migration_0033_down[] =
    "SELECT 1;";

static const char migration_0034_up[] =
    "UPDATE streams\n"
    "   SET url = substr(url, 1, instr(url, '://') + 2) ||\n"
    "             substr(substr(url, instr(url, '://') + 3),\n"
    "                    instr(substr(url, instr(url, '://') + 3), '@') + 1),\n"
    "       onvif_username = CASE\n"
    "           WHEN COALESCE(onvif_username, '') = '' THEN\n"
    "               CASE\n"
    "                   WHEN instr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                     1,\n"
    "                                     instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                              ':') > 0 THEN\n"
    "                       substr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                           1,\n"
    "                                           instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                              1,\n"
    "                              instr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                                  1,\n"
    "                                                  instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                                     ':') - 1)\n"
    "                   ELSE\n"
    "                       substr(substr(url, instr(url, '://') + 3),\n"
    "                              1,\n"
    "                              instr(substr(url, instr(url, '://') + 3), '@') - 1)\n"
    "               END\n"
    "           ELSE onvif_username\n"
    "       END,\n"
    "       onvif_password = CASE\n"
    "           WHEN COALESCE(onvif_password, '') = '' THEN\n"
    "               CASE\n"
    "                   WHEN instr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                     1,\n"
    "                                     instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                              ':') > 0 THEN\n"
    "                       substr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                           1,\n"
    "                                           instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                              instr(substr(substr(url, instr(url, '://') + 3),\n"
    "                                                  1,\n"
    "                                                  instr(substr(url, instr(url, '://') + 3), '@') - 1),\n"
    "                                     ':') + 1)\n"
    "                   ELSE ''\n"
    "               END\n"
    "           ELSE onvif_password\n"
    "       END\n"
    " WHERE instr(url, '://') > 0\n"
    "   AND instr(substr(url, instr(url, '://') + 3), '@') > 0\n"
    "   AND (\n"
    "        is_onvif = 1 OR\n"
    "        COALESCE(onvif_profile, '') != '' OR\n"
    "        COALESCE(onvif_username, '') != '' OR\n"
    "        COALESCE(onvif_password, '') != '' OR\n"
    "        COALESCE(onvif_port, 0) > 0\n"
    "   );";

static const char migration_0034_down[] =
    "SELECT 1;";

static const char migration_0035_up[] =
    "ALTER TABLE sessions ADD COLUMN last_activity_at INTEGER;\n"
    "UPDATE sessions\n"
    "   SET last_activity_at = COALESCE(last_activity_at, created_at, strftime('%s', 'now'));\n"
    "ALTER TABLE sessions ADD COLUMN idle_expires_at INTEGER;\n"
    "UPDATE sessions\n"
    "   SET idle_expires_at = COALESCE(idle_expires_at, expires_at, strftime('%s', 'now'));\n"
    "CREATE INDEX IF NOT EXISTS idx_sessions_user_last_activity\n"
    "    ON sessions(user_id, last_activity_at DESC);\n"
    "CREATE TABLE IF NOT EXISTS trusted_devices (\n"
    "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
    "    user_id INTEGER NOT NULL,\n"
    "    token TEXT NOT NULL UNIQUE,\n"
    "    ip_address TEXT,\n"
    "    user_agent TEXT,\n"
    "    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),\n"
    "    last_used_at INTEGER,\n"
    "    expires_at INTEGER NOT NULL,\n"
    "    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n"
    ");\n"
    "CREATE INDEX IF NOT EXISTS idx_trusted_devices_user_id ON trusted_devices(user_id);\n"
    "CREATE INDEX IF NOT EXISTS idx_trusted_devices_token ON trusted_devices(token);\n"
    "CREATE INDEX IF NOT EXISTS idx_trusted_devices_expires_at ON trusted_devices(expires_at);";

static const char migration_0035_down[] =
    "DROP INDEX IF EXISTS idx_trusted_devices_expires_at;\n"
    "DROP INDEX IF EXISTS idx_trusted_devices_token;\n"
    "DROP INDEX IF EXISTS idx_trusted_devices_user_id;\n"
    "DROP TABLE IF EXISTS trusted_devices;\n"
    "DROP INDEX IF EXISTS idx_sessions_user_last_activity;";

static const char migration_0036_up[] =
    "ALTER TABLE streams ADD COLUMN admin_url TEXT DEFAULT '';";

static const char migration_0036_down[] =
    "SELECT 1;";

static const char migration_0037_up[] =
    "ALTER TABLE users ADD COLUMN allowed_login_cidrs TEXT DEFAULT NULL;";

static const char migration_0037_down[] =
    "SELECT 1;";

static const char migration_0038_up[] =
    "ALTER TABLE streams ADD COLUMN privacy_mode INTEGER DEFAULT 0;";

static const char migration_0038_down[] =
    "SELECT 1;";

static const char migration_0039_up[] =
    "ALTER TABLE streams ADD COLUMN motion_trigger_source TEXT DEFAULT '';";

static const char migration_0039_down[] =
    "SELECT 1;";

static const char migration_0040_up[] =
    "ALTER TABLE streams ADD COLUMN go2rtc_source_override TEXT DEFAULT '';";

static const char migration_0040_down[] =
    "SELECT 1;";

static const migration_t embedded_migrations_data[] = {
    {
        .version = "0001",
        .description = "initial_schema",
        .sql_up = migration_0001_up,
        .sql_down = migration_0001_down,
        .is_embedded = true
    },
    {
        .version = "0002",
        .description = "add_detection_columns",
        .sql_up = migration_0002_up,
        .sql_down = migration_0002_down,
        .is_embedded = true
    },
    {
        .version = "0003",
        .description = "add_protocol_onvif",
        .sql_up = migration_0003_up,
        .sql_down = migration_0003_down,
        .is_embedded = true
    },
    {
        .version = "0004",
        .description = "add_record_audio",
        .sql_up = migration_0004_up,
        .sql_down = migration_0004_down,
        .is_embedded = true
    },
    {
        .version = "0005",
        .description = "add_detections_table",
        .sql_up = migration_0005_up,
        .sql_down = migration_0005_down,
        .is_embedded = true
    },
    {
        .version = "0006",
        .description = "add_users_table",
        .sql_up = migration_0006_up,
        .sql_down = migration_0006_down,
        .is_embedded = true
    },
    {
        .version = "0007",
        .description = "add_motion_settings",
        .sql_up = migration_0007_up,
        .sql_down = migration_0007_down,
        .is_embedded = true
    },
    {
        .version = "0008",
        .description = "add_zones_table",
        .sql_up = migration_0008_up,
        .sql_down = migration_0008_down,
        .is_embedded = true
    },
    {
        .version = "0009",
        .description = "add_detection_tracking",
        .sql_up = migration_0009_up,
        .sql_down = migration_0009_down,
        .is_embedded = true
    },
    {
        .version = "0010",
        .description = "add_trigger_type",
        .sql_up = migration_0010_up,
        .sql_down = migration_0010_down,
        .is_embedded = true
    },
    {
        .version = "0011",
        .description = "add_detection_api_url",
        .sql_up = migration_0011_up,
        .sql_down = migration_0011_down,
        .is_embedded = true
    },
    {
        .version = "0012",
        .description = "add_backchannel",
        .sql_up = migration_0012_up,
        .sql_down = migration_0012_down,
        .is_embedded = true
    },
    {
        .version = "0013",
        .description = "add_retention_policy",
        .sql_up = migration_0013_up,
        .sql_down = migration_0013_down,
        .is_embedded = true
    },
    {
        .version = "0014",
        .description = "add_ptz_support",
        .sql_up = migration_0014_up,
        .sql_down = migration_0014_down,
        .is_embedded = true
    },
    {
        .version = "0015",
        .description = "add_buffer_strategy",
        .sql_up = migration_0015_up,
        .sql_down = migration_0015_down,
        .is_embedded = true
    },
    {
        .version = "0016",
        .description = "add_onvif_credentials",
        .sql_up = migration_0016_up,
        .sql_down = migration_0016_down,
        .is_embedded = true
    },
    {
        .version = "0017",
        .description = "add_recording_id_to_detections",
        .sql_up = migration_0017_up,
        .sql_down = migration_0017_down,
        .is_embedded = true
    },
    {
        .version = "0018",
        .description = "add_session_tracking",
        .sql_up = migration_0018_up,
        .sql_down = migration_0018_down,
        .is_embedded = true
    },
    {
        .version = "0019",
        .description = "add_password_change_locked",
        .sql_up = migration_0019_up,
        .sql_down = migration_0019_down,
        .is_embedded = true
    },
    {
        .version = "0020",
        .description = "add_motion_recording_config",
        .sql_up = migration_0020_up,
        .sql_down = migration_0020_down,
        .is_embedded = true
    },
    {
        .version = "0021",
        .description = "add_totp_mfa",
        .sql_up = migration_0021_up,
        .sql_down = migration_0021_down,
        .is_embedded = true
    },
    {
        .version = "0022",
        .description = "fix_detection_zones_table",
        .sql_up = migration_0022_up,
        .sql_down = migration_0022_down,
        .is_embedded = true
    },
    {
        .version = "0023",
        .description = "add_detection_object_filters",
        .sql_up = migration_0023_up,
        .sql_down = migration_0023_down,
        .is_embedded = true
    },
    {
        .version = "0024",
        .description = "enhanced_storage_management",
        .sql_up = migration_0024_up,
        .sql_down = migration_0024_down,
        .is_embedded = true
    },
    {
        .version = "0025",
        .description = "add_detection_performance_indexes",
        .sql_up = migration_0025_up,
        .sql_down = migration_0025_down,
        .is_embedded = true
    },
    {
        .version = "0026",
        .description = "add_recording_schedule",
        .sql_up = migration_0026_up,
        .sql_down = migration_0026_down,
        .is_embedded = true
    },
    {
        .version = "0027",
        .description = "add_stream_group",
        .sql_up = migration_0027_up,
        .sql_down = migration_0027_down,
        .is_embedded = true
    },
    {
        .version = "0028",
        .description = "add_stream_tags",
        .sql_up = migration_0028_up,
        .sql_down = migration_0028_down,
        .is_embedded = true
    },
    {
        .version = "0029",
        .description = "add_user_allowed_tags",
        .sql_up = migration_0029_up,
        .sql_down = migration_0029_down,
        .is_embedded = true
    },
    {
        .version = "0030",
        .description = "add_system_settings",
        .sql_up = migration_0030_up,
        .sql_down = migration_0030_down,
        .is_embedded = true
    },
    {
        .version = "0031",
        .description = "add_onvif_port",
        .sql_up = migration_0031_up,
        .sql_down = migration_0031_down,
        .is_embedded = true
    },
    {
        .version = "0032",
        .description = "add_recording_tags",
        .sql_up = migration_0032_up,
        .sql_down = migration_0032_down,
        .is_embedded = true
    },
    {
        .version = "0033",
        .description = "cleanup_blank_stream_names",
        .sql_up = migration_0033_up,
        .sql_down = migration_0033_down,
        .is_embedded = true
    },
    {
        .version = "0034",
        .description = "repair_onvif_embedded_credentials",
        .sql_up = migration_0034_up,
        .sql_down = migration_0034_down,
        .is_embedded = true
    },
    {
        .version = "0035",
        .description = "modernize_auth_sessions",
        .sql_up = migration_0035_up,
        .sql_down = migration_0035_down,
        .is_embedded = true
    },
    {
        .version = "0036",
        .description = "add_stream_admin_url",
        .sql_up = migration_0036_up,
        .sql_down = migration_0036_down,
        .is_embedded = true
    },
    {
        .version = "0037",
        .description = "add_user_allowed_login_cidrs",
        .sql_up = migration_0037_up,
        .sql_down = migration_0037_down,
        .is_embedded = true
    },
    {
        .version = "0038",
        .description = "add_privacy_mode",
        .sql_up = migration_0038_up,
        .sql_down = migration_0038_down,
        .is_embedded = true
    },
    {
        .version = "0039",
        .description = "add_motion_trigger_source",
        .sql_up = migration_0039_up,
        .sql_down = migration_0039_down,
        .is_embedded = true
    },
    {
        .version = "0040",
        .description = "add_go2rtc_source_override",
        .sql_up = migration_0040_up,
        .sql_down = migration_0040_down,
        .is_embedded = true
    },
};

#define EMBEDDED_MIGRATIONS_COUNT 40

#endif /* DB_EMBEDDED_MIGRATIONS_H */