db_view/DBconfig/sqlfiles/20251121_create_scripts_table.sql

-- Migration: create `scripts` table (destructive, does NOT preserve old `sql_scripts` data)
-- Generated: 2025-11-21

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;

-- Drop legacy table if exists (destructive)
DROP TABLE IF EXISTS sql_scripts;

-- Create new `scripts` table with extended columns
CREATE TABLE IF NOT EXISTS scripts (
    id TEXT PRIMARY KEY,
    connection_id TEXT NOT NULL,
    group_id TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    content TEXT,
    favorite INTEGER DEFAULT 0,
    language TEXT DEFAULT '',
    metadata TEXT DEFAULT '{}', -- JSON object as TEXT
    tags TEXT DEFAULT '[]',     -- JSON array as TEXT
    enabled INTEGER DEFAULT 1,
    owner TEXT DEFAULT '',
    checksum TEXT DEFAULT '',
    execution_count INTEGER DEFAULT 0,
    last_executed TEXT,
    last_run_status TEXT DEFAULT '',
    created_at DATETIME,
    updated_at DATETIME
);

-- Create index for lookup by connection
CREATE INDEX IF NOT EXISTS idx_scripts_connection_id ON scripts(connection_id);
CREATE INDEX IF NOT EXISTS idx_scripts_group_id ON scripts(group_id);

COMMIT;
PRAGMA foreign_keys = ON;