CREATE TABLE IF NOT EXISTS etl_connections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NULL,
    instancia_id INT NULL,
    alias VARCHAR(100) NOT NULL,
    friendly_name VARCHAR(191) NOT NULL,
    db_host VARCHAR(191) NOT NULL DEFAULT '127.0.0.1',
    db_port INT NOT NULL DEFAULT 3307,
    db_name VARCHAR(191) NOT NULL,
    db_user VARCHAR(191) NOT NULL,
    db_pass_enc TEXT NULL,
    ssh_host VARCHAR(191) NULL,
    ssh_user VARCHAR(100) NULL,
    ssh_key_path VARCHAR(500) NULL,
    remote_mysql_host VARCHAR(191) NULL,
    remote_mysql_port INT NULL,
    origin_type VARCHAR(50) NOT NULL DEFAULT 'oracle_cloud',
    onboarding_status VARCHAR(50) NOT NULL DEFAULT 'source_registered',
    activo TINYINT(1) NOT NULL DEFAULT 1,
    last_test_at DATETIME NULL,
    last_test_status VARCHAR(20) NULL,
    last_test_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uq_etl_connections_alias (alias),
    KEY idx_etl_connections_cliente (cliente_id),
    KEY idx_etl_connections_instancia (instancia_id),
    CONSTRAINT fk_etl_conn_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_conn_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_object_meta (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NULL,
    instancia_id INT NULL,
    alias VARCHAR(100) NOT NULL,
    remote_db VARCHAR(191) NOT NULL,
    object_name VARCHAR(191) NOT NULL,
    object_type VARCHAR(20) NULL,
    comment TEXT NULL,
    procesos TEXT NULL,
    dest_table VARCHAR(191) NULL,
    last_action_at DATETIME NULL,
    last_action_type VARCHAR(20) NULL,
    last_action_rows INT NULL,
    auto_update TINYINT(1) NOT NULL DEFAULT 0,
    auto_update_cron VARCHAR(191) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    UNIQUE KEY uq_etl_meta (alias, remote_db, object_name),
    KEY idx_etl_meta_cliente (cliente_id),
    KEY idx_etl_meta_instancia (instancia_id),
    CONSTRAINT fk_etl_meta_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_meta_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_processes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NULL,
    instancia_id INT NULL,
    name VARCHAR(191) NOT NULL,
    description TEXT NULL,
    group_name VARCHAR(191) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL,
    KEY idx_etl_processes_cliente (cliente_id),
    KEY idx_etl_processes_instancia (instancia_id),
    CONSTRAINT fk_etl_proc_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_proc_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_process_objects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    process_id INT NOT NULL,
    alias VARCHAR(100) NOT NULL,
    remote_db VARCHAR(191) NOT NULL,
    object_name VARCHAR(191) NOT NULL,
    dest_table VARCHAR(191) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_etl_proc_obj (process_id, alias, remote_db, object_name),
    CONSTRAINT fk_etl_proc_obj_process FOREIGN KEY (process_id) REFERENCES etl_processes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_process_docs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    process_id INT NOT NULL,
    title VARCHAR(191) NOT NULL,
    url VARCHAR(500) NULL,
    file_name VARCHAR(255) NULL,
    file_path VARCHAR(500) NULL,
    mime_type VARCHAR(191) NULL,
    file_size BIGINT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_etl_proc_docs_process FOREIGN KEY (process_id) REFERENCES etl_processes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id VARCHAR(64) NOT NULL,
    cliente_id INT NULL,
    instancia_id INT NULL,
    alias VARCHAR(100) NOT NULL,
    dbname VARCHAR(191) NOT NULL,
    objeto VARCHAR(191) NOT NULL,
    target VARCHAR(191) NOT NULL,
    accion VARCHAR(20) NOT NULL,
    pk_col VARCHAR(191) NULL,
    inc_col VARCHAR(191) NULL,
    batch_size INT NOT NULL DEFAULT 1000,
    estado VARCHAR(20) NOT NULL DEFAULT 'queued',
    total_rows INT NOT NULL DEFAULT 0,
    msg TEXT NULL,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_etl_progress_job (job_id),
    KEY idx_etl_progress_instancia (instancia_id),
    CONSTRAINT fk_etl_progress_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_progress_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_run_marks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NULL,
    instancia_id INT NULL,
    alias VARCHAR(100) NOT NULL,
    objeto VARCHAR(191) NOT NULL,
    pk_col VARCHAR(191) NULL,
    inc_col VARCHAR(191) NULL,
    last_value VARCHAR(255) NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_etl_mark (instancia_id, alias, objeto),
    CONSTRAINT fk_etl_marks_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_marks_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS etl_license_snapshots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NULL,
    instancia_id INT NULL,
    alias VARCHAR(100) NOT NULL,
    remote_db VARCHAR(191) NOT NULL,
    source_table VARCHAR(191) NOT NULL,
    row_hash CHAR(64) NOT NULL,
    license_key VARCHAR(191) NULL,
    app_name VARCHAR(191) NULL,
    user_limit INT NULL,
    active_users INT NULL,
    valid_from DATE NULL,
    valid_to DATE NULL,
    status VARCHAR(50) NULL,
    raw_json JSON NULL,
    captured_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_etl_license_snapshot (alias, remote_db, source_table, row_hash),
    KEY idx_etl_license_instancia (instancia_id),
    KEY idx_etl_license_valid_to (valid_to),
    CONSTRAINT fk_etl_license_cliente FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE SET NULL,
    CONSTRAINT fk_etl_license_instancia FOREIGN KEY (instancia_id) REFERENCES instancias(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
