PANEL_BASES_ANEXO24/database/schema.sql

-- Panel Transmitiras: usuarios, permisos y sesiones (mismo modelo que
-- ~/dev/a24c/backend/api/v1/modules/dashboard — tablas en inglés, esquema a24c).
-- Ejecutar en PostgreSQL (misma base donde está el catálogo ControlDesk, p. ej. CONTROLDESK).

CREATE SCHEMA IF NOT EXISTS a24c;

CREATE TABLE IF NOT EXISTS a24c.dashboard_users (
    id              SERIAL PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    email           VARCHAR(100) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(100),
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_admin        BOOLEAN NOT NULL DEFAULT false,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_access_at  TIMESTAMPTZ,
    CONSTRAINT chk_dashboard_users_username_length CHECK (char_length(username) >= 3)
);

CREATE TABLE IF NOT EXISTS a24c.dashboard_user_database_permissions (
    id                     SERIAL PRIMARY KEY,
    dashboard_user_id      INTEGER NOT NULL REFERENCES a24c.dashboard_users (id) ON DELETE CASCADE,
    database_name          VARCHAR(255) NOT NULL,
    can_view               BOOLEAN NOT NULL DEFAULT true,
    can_download_backup    BOOLEAN NOT NULL DEFAULT false,
    can_restore            BOOLEAN NOT NULL DEFAULT false,
    assigned_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (dashboard_user_id, database_name)
);

CREATE TABLE IF NOT EXISTS a24c.dashboard_sessions (
    id                  SERIAL PRIMARY KEY,
    dashboard_user_id   INTEGER NOT NULL REFERENCES a24c.dashboard_users (id) ON DELETE CASCADE,
    token               VARCHAR(255) UNIQUE NOT NULL,
    ip_address          VARCHAR(50),
    user_agent          TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at          TIMESTAMPTZ NOT NULL,
    is_active           BOOLEAN NOT NULL DEFAULT true
);

CREATE INDEX IF NOT EXISTS idx_a24c_dashboard_user_db_perm_user
    ON a24c.dashboard_user_database_permissions (dashboard_user_id);
CREATE INDEX IF NOT EXISTS idx_a24c_dashboard_user_db_perm_dbname
    ON a24c.dashboard_user_database_permissions (database_name);
CREATE INDEX IF NOT EXISTS idx_a24c_dashboard_users_active ON a24c.dashboard_users (is_active);
CREATE INDEX IF NOT EXISTS idx_a24c_dashboard_sessions_token ON a24c.dashboard_sessions (token);
CREATE INDEX IF NOT EXISTS idx_a24c_dashboard_sessions_user ON a24c.dashboard_sessions (dashboard_user_id);

COMMENT ON TABLE a24c.dashboard_users IS 'Operadores del panel Transmitiras (JWT/bcrypt). Paridad api/v1/modules/dashboard/users.';
COMMENT ON TABLE a24c.dashboard_user_database_permissions IS 'Permisos por base de datos. Paridad dashboard/user_database_permissions.';
COMMENT ON TABLE a24c.dashboard_sessions IS 'Sesiones persistidas (opcional; el panel usa JWT en cookie). Paridad dashboard/sessions.';

-- Usuario admin por defecto (cambiar password_hash tras primer arranque; ver scripts/generate-password-hash.js)
INSERT INTO a24c.dashboard_users (username, email, password_hash, full_name, is_admin, is_active)
VALUES (
    'admin',
    'admin@aduanasoft.com',
    '$2b$10$rKvvkYpNwF5J5K5J5J5J5uX5X5X5X5X5X5X5X5X5X5X5X5X5X5X5X',
    'Administrador del Sistema',
    true,
    true
)
ON CONFLICT (username) DO NOTHING;

-- ============================================================================
-- Integración CloudRestoreAS: servidores de restauración y bitácora de jobs.
-- Equivalente versionado en database/migrations/001_restore_targets.{up,down}.sql
-- ============================================================================

-- Servidores SQL Server destino de restauración (Alfa, Omega, Gamma). Son máquinas
-- EXTERNAS independientes: el .bak se transfiere por SFTP/SSH al servidor y el SQL Server
-- restaura desde su disco local. Cada base (a24c.database_nodes.restore_target_id) se asigna
-- a uno de ellos. Las contraseñas (SQL y SSH) se guardan cifradas con AES-256-GCM, nunca en plano.
CREATE TABLE IF NOT EXISTS a24c.restore_targets (
    id                      SERIAL PRIMARY KEY,
    name                    VARCHAR(120) NOT NULL UNIQUE,  -- Alfa | Omega | Gamma
    server_ip               VARCHAR(255),            -- SQL Server: IP/hostname; acepta "ip,puerto"
    sql_username            VARCHAR(128),
    sql_password_encrypted  TEXT,                    -- sobre gcm:iv:tag:ciphertext
    data_folder             VARCHAR(500),            -- ruta .mdf/.ldf EN el server remoto (C:\SQLData)
    ssh_host                VARCHAR(255),            -- host SSH del servidor (suele ser el mismo equipo)
    ssh_port                INTEGER DEFAULT 22,
    ssh_username            VARCHAR(128),
    ssh_password_encrypted  TEXT,                    -- credencial SSH cifrada (AES-256-GCM)
    remote_inbox_path       VARCHAR(500),            -- ruta en el server donde se sube el .bak y se restaura (C:\RestoreInbox)
    notes                   TEXT,
    -- Características de hardware (opcionales, capturadas a mano). Se usan para distribuir bases
    -- por capacidad; el disco es la capacidad que manda. NULL = sin capturar.
    os                      VARCHAR(50),             -- Sistema operativo (Linux/Windows/…)
    ram_gb                  INTEGER,                 -- RAM en GB
    disk_gb                 INTEGER,                 -- Disco en GB
    location                VARCHAR(255),            -- Ubicación (ej: Kansas City, United States)
    size_category           VARCHAR(20),             -- Etiqueta: Chico | Mediano | Grande
    created_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Tres servidores fijos de partida (editables). Datos de conexión vacíos hasta configurarse.
INSERT INTO a24c.restore_targets (name) VALUES ('Alfa'), ('Omega'), ('Gamma')
ON CONFLICT (name) DO NOTHING;

-- Asignación servidor de restauración ↔ base de datos (la tabla database_nodes la aprovisiona
-- el backend a24c; aquí solo se agrega la columna de asignación).
ALTER TABLE a24c.database_nodes
    ADD COLUMN IF NOT EXISTS restore_target_id INTEGER
    REFERENCES a24c.restore_targets (id) ON DELETE SET NULL;

-- Backfill único: la credencial SQL del nodo (database_nodes.sql_password, columna de a24c) se
-- copia del restaurador asignado como sobre cifrado gcm: (el panel lo descifra al conectar).
-- Idempotente: solo rellena nodos ya asignados sin contraseña; no pisa valores existentes.
-- Guardado por si a24c aún no aprovisionó la columna sql_password (evita romper el init).
-- Rollback: UPDATE a24c.database_nodes SET sql_password = NULL;
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'a24c'
          AND table_name = 'database_nodes'
          AND column_name = 'sql_password'
    ) THEN
        UPDATE a24c.database_nodes n
        SET sql_password = rt.sql_password_encrypted
        FROM a24c.restore_targets rt
        WHERE n.restore_target_id = rt.id
          AND rt.sql_password_encrypted IS NOT NULL
          AND (n.sql_password IS NULL OR n.sql_password = '');
    END IF;
END $$;

-- Bitácora de restauraciones reportadas por CloudRestoreAS.
CREATE TABLE IF NOT EXISTS a24c.restore_job_logs (
    id                  SERIAL PRIMARY KEY,
    filename            VARCHAR(500) NOT NULL,
    restore_target_id   INTEGER REFERENCES a24c.restore_targets (id) ON DELETE SET NULL,
    db_name             VARCHAR(255),
    status              VARCHAR(20) NOT NULL CHECK (status IN ('completed', 'failed', 'forwarded')),
    duration_ms         INTEGER,
    error_message       TEXT,
    restored_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_a24c_restore_job_logs_target
    ON a24c.restore_job_logs (restore_target_id);
CREATE INDEX IF NOT EXISTS idx_a24c_restore_job_logs_restored_at
    ON a24c.restore_job_logs (restored_at);
CREATE INDEX IF NOT EXISTS idx_a24c_restore_job_logs_status
    ON a24c.restore_job_logs (status);
-- Bitácora por servidor en el panel: últimas N restauraciones de un target.
CREATE INDEX IF NOT EXISTS idx_a24c_restore_job_logs_target_date
    ON a24c.restore_job_logs (restore_target_id, restored_at DESC);

COMMENT ON TABLE a24c.restore_targets IS 'Servidores SQL Server destino de restauración (CloudRestoreAS). Contraseña cifrada AES-256-GCM.';
COMMENT ON TABLE a24c.restore_job_logs IS 'Bitácora de restauraciones reportadas por CloudRestoreAS.';

-- Equivalente versionado en database/migrations/002_cloudrestore_status.{up,down}.sql
-- Carpeta de entrada reportada por CloudRestoreAS (solo lectura en el panel).
CREATE TABLE IF NOT EXISTS a24c.cloudrestore_status (
    id              SERIAL PRIMARY KEY,
    instance_key    VARCHAR(120) NOT NULL UNIQUE DEFAULT 'default',
    input_folder    VARCHAR(500) NOT NULL,
    host_name       VARCHAR(255),
    app_version     VARCHAR(50),
    reported_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Identidad del build instalado, reportada por el agente en instance-config. Es la fuente
-- autoritativa para decidir qué artefacto de CRAS le toca a cada servidor; un agente anterior
-- a 1.1.0 no las manda y quedan NULL (el panel cae a restore_targets.os).
ALTER TABLE a24c.cloudrestore_status ADD COLUMN IF NOT EXISTS processed_folder VARCHAR(500);
ALTER TABLE a24c.cloudrestore_status ADD COLUMN IF NOT EXISTS platform VARCHAR(20);
ALTER TABLE a24c.cloudrestore_status ADD COLUMN IF NOT EXISTS arch VARCHAR(20);
-- Carpeta del ejecutable (APP_DIR). Distinta de input_folder: ahí vive config/.env.
ALTER TABLE a24c.cloudrestore_status ADD COLUMN IF NOT EXISTS install_path VARCHAR(500);

COMMENT ON TABLE a24c.cloudrestore_status IS
    'Carpeta de entrada vigente reportada por CloudRestoreAS. Solo lectura en el panel.';

-- ============================================================================
-- Distribución de versiones de CloudRestoreAS (migración a24c e2f3a4b5c6d7).
--
-- Los binarios NO viven aquí: se publican en el registro de paquetes genéricos de Gitea
-- (ADUANASOFT/generic/cloudrestoreas/<version>) y el panel los cachea en disco. Un artefacto
-- pesa ~270 MB, así que guardarlo como BLOB en la base no es viable.
-- ============================================================================

-- Catálogo de versiones publicadas (metadatos + el sha256 que calcula Gitea).
CREATE TABLE IF NOT EXISTS a24c.cras_releases (
    id             SERIAL PRIMARY KEY,
    version        VARCHAR(50)  NOT NULL,
    platform       VARCHAR(20)  NOT NULL,
    arch           VARCHAR(20)  NOT NULL DEFAULT 'x86_64',
    file_name      VARCHAR(255) NOT NULL,
    file_size      BIGINT,
    sha256         VARCHAR(64),
    gitea_package  VARCHAR(120) NOT NULL DEFAULT 'cloudrestoreas',
    changelog      TEXT,
    is_active      BOOLEAN NOT NULL DEFAULT FALSE,
    published_at   TIMESTAMPTZ,
    discovered_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT cras_releases_platform_check CHECK (platform IN ('windows', 'linux')),
    CONSTRAINT cras_releases_version_platform_arch_key UNIQUE (version, platform, arch)
);

-- Una sola versión activa POR plataforma+arquitectura, garantizado en la base: así Windows y
-- Linux pueden tener cada una la suya, a diferencia de un is_active singleton global.
CREATE UNIQUE INDEX IF NOT EXISTS idx_a24c_cras_releases_active
    ON a24c.cras_releases (platform, arch) WHERE is_active;
CREATE INDEX IF NOT EXISTS idx_a24c_cras_releases_version
    ON a24c.cras_releases (version);

-- Bitácora de instalaciones remotas, con progreso paso a paso para que la UI lo siga.
CREATE TABLE IF NOT EXISTS a24c.cras_install_runs (
    id                SERIAL PRIMARY KEY,
    restore_target_id INTEGER REFERENCES a24c.restore_targets (id) ON DELETE SET NULL,
    release_id        INTEGER REFERENCES a24c.cras_releases (id) ON DELETE SET NULL,
    version           VARCHAR(50),
    platform          VARCHAR(20),
    mode              VARCHAR(20) NOT NULL,
    status            VARCHAR(20) NOT NULL,
    install_path      VARCHAR(500),
    steps             JSONB NOT NULL DEFAULT '[]'::jsonb,
    error_message     TEXT,
    started_by        VARCHAR(128),
    started_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_at       TIMESTAMPTZ,
    CONSTRAINT cras_install_runs_mode_check CHECK (mode IN ('install', 'update')),
    CONSTRAINT cras_install_runs_status_check CHECK (status IN ('running', 'completed', 'failed'))
);

CREATE INDEX IF NOT EXISTS idx_a24c_cras_install_runs_target
    ON a24c.cras_install_runs (restore_target_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_a24c_cras_install_runs_running
    ON a24c.cras_install_runs (restore_target_id) WHERE status = 'running';

COMMENT ON TABLE a24c.cras_releases IS
    'Catálogo de versiones de CloudRestoreAS publicadas en Gitea. Solo metadatos; los binarios viven en Gitea.';
COMMENT ON TABLE a24c.cras_install_runs IS
    'Bitácora de instalaciones remotas de CloudRestoreAS, con progreso paso a paso en steps.';