SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS user_preferences (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    dashboard_layout JSON NULL,
    items_per_page INT UNSIGNED NOT NULL DEFAULT 25,
    timezone VARCHAR(80) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_up_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_up_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_exports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    report_type VARCHAR(100) NOT NULL,
    format ENUM('csv','json') NOT NULL DEFAULT 'csv',
    file_path VARCHAR(500) NOT NULL,
    status ENUM('ready','failed','expired') NOT NULL DEFAULT 'ready',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    CONSTRAINT fk_re_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_re_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cron_heartbeats (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    worker_name VARCHAR(120) NOT NULL,
    host_name VARCHAR(190) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    processed_jobs INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('running','ok','error') NOT NULL DEFAULT 'running',
    message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_heartbeat_worker(worker_name,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
