CREATE TABLE IF NOT EXISTS tenant_health_status (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NOT NULL,
    health_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    storage_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    database_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    update_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    cache_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    api_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    cron_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    subscription_status VARCHAR(50) NOT NULL DEFAULT 'unknown',
    warning_count INT(11) NOT NULL DEFAULT 0,
    error_count INT(11) NOT NULL DEFAULT 0,
    last_checked_at DATETIME NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_tenant_health_status_tenant_id (tenant_id),
    KEY idx_tenant_health_status_health_status (health_status),
    KEY idx_tenant_health_status_subscription_status (subscription_status),
    KEY idx_tenant_health_status_last_checked_at (last_checked_at),
    CONSTRAINT fk_tenant_health_status_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS telemetry_events (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NULL,
    event_type VARCHAR(100) NOT NULL,
    severity VARCHAR(50) NOT NULL DEFAULT 'info',
    event_key VARCHAR(150) NOT NULL,
    message TEXT NOT NULL,
    context_json LONGTEXT NULL,
    source VARCHAR(100) NOT NULL DEFAULT 'monitoring',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_telemetry_events_tenant_id (tenant_id),
    KEY idx_telemetry_events_severity (severity),
    KEY idx_telemetry_events_event_key (event_key),
    KEY idx_telemetry_events_source (source),
    KEY idx_telemetry_events_created_at (created_at),
    CONSTRAINT fk_telemetry_events_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS monitoring_alerts (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NULL,
    alert_type VARCHAR(100) NOT NULL,
    severity VARCHAR(50) NOT NULL DEFAULT 'warning',
    title VARCHAR(191) NOT NULL,
    message TEXT NOT NULL,
    is_resolved TINYINT(1) NOT NULL DEFAULT 0,
    resolved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_monitoring_alerts_tenant_id (tenant_id),
    KEY idx_monitoring_alerts_alert_type (alert_type),
    KEY idx_monitoring_alerts_severity (severity),
    KEY idx_monitoring_alerts_is_resolved (is_resolved),
    KEY idx_monitoring_alerts_created_at (created_at),
    CONSTRAINT fk_monitoring_alerts_tenant_id
        FOREIGN KEY (tenant_id) REFERENCES tenants (id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;