-- NICC Style Config - Schema v1
-- Gestión centralizada de style.ini para 250+ sistemas

CREATE TABLE IF NOT EXISTS style_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT NOT NULL UNIQUE,
    color TEXT NOT NULL DEFAULT '#3b82f6',
    description TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS style_origins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL,
    system_name TEXT NOT NULL,
    system_path TEXT NOT NULL,
    system_title TEXT,
    template TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(group_id),
    FOREIGN KEY (group_id) REFERENCES style_groups(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS style_destinations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id INTEGER NOT NULL,
    system_name TEXT NOT NULL,
    system_path TEXT NOT NULL,
    system_title TEXT,
    template TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(group_id, system_name),
    FOREIGN KEY (group_id) REFERENCES style_groups(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS style_backups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    system_name TEXT NOT NULL,
    system_path TEXT NOT NULL,
    template TEXT NOT NULL,
    style_ini_content TEXT NOT NULL,
    backup_reason TEXT,
    group_label TEXT,
    version INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS style_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    action TEXT NOT NULL,
    group_label TEXT,
    details TEXT,
    systems_affected INTEGER DEFAULT 0,
    status TEXT DEFAULT 'ok',
    user_id TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS style_systems_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    system_name TEXT NOT NULL UNIQUE,
    system_path TEXT NOT NULL,
    system_title TEXT,
    template TEXT,
    has_style_ini_20 INTEGER DEFAULT 0,
    has_style_ini_21 INTEGER DEFAULT 0,
    is_writable INTEGER DEFAULT 0,
    scanned_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_origins_group ON style_origins(group_id);
CREATE INDEX IF NOT EXISTS idx_destinations_group ON style_destinations(group_id);
CREATE INDEX IF NOT EXISTS idx_backups_system ON style_backups(system_name);
CREATE INDEX IF NOT EXISTS idx_backups_date ON style_backups(created_at);
CREATE INDEX IF NOT EXISTS idx_logs_action ON style_logs(action);
CREATE INDEX IF NOT EXISTS idx_logs_date ON style_logs(created_at);
-- idx_cache_name omitted: system_name UNIQUE constraint already creates an implicit index
