CREATE TABLE IF NOT EXISTS visu_csv_export_jobs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    selection_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    name VARCHAR(190) NOT NULL,
    export_kind ENUM('trend_csv', 'lastvalues_csv') NOT NULL DEFAULT 'trend_csv',
    csv_delimiter VARCHAR(4) NOT NULL DEFAULT ';',
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    schedule_kind ENUM('interval', 'daily', 'weekly', 'monthly') NOT NULL DEFAULT 'daily',
    interval_minutes INT UNSIGNED NOT NULL DEFAULT 60,
    time_of_day CHAR(5) NOT NULL DEFAULT '00:00',
    weekdays VARCHAR(32) NOT NULL DEFAULT '',
    month_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
    target_dir VARCHAR(255) NOT NULL DEFAULT 'csv_exports',
    filename_pattern VARCHAR(255) NOT NULL DEFAULT '{selection}_{yyyy}-{mm}-{dd}_{hh}-{ii}-{ss}.csv',
    retention_days INT UNSIGNED NOT NULL DEFAULT 30,
    last_run_at DATETIME NULL DEFAULT NULL,
    next_run_at DATETIME NULL DEFAULT NULL,
    last_status ENUM('idle', 'running', 'success', 'error') NOT NULL DEFAULT 'idle',
    last_error TEXT NULL DEFAULT NULL,
    created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_visu_csv_export_jobs_selection (selection_id),
    KEY idx_visu_csv_export_jobs_project (project_id),
    KEY idx_visu_csv_export_jobs_enabled_next_run (enabled, next_run_at),
    KEY idx_visu_csv_export_jobs_status (last_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visu_csv_export_files (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    project_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    selection_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('queued', 'running', 'success', 'error', 'deleted') NOT NULL DEFAULT 'queued',
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    row_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    export_started_at DATETIME NULL DEFAULT NULL,
    export_finished_at DATETIME NULL DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    error_message TEXT NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_visu_csv_export_files_job (job_id),
    KEY idx_visu_csv_export_files_selection (selection_id),
    KEY idx_visu_csv_export_files_project (project_id),
    KEY idx_visu_csv_export_files_status (status),
    KEY idx_visu_csv_export_files_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;