create_waf_tables.sql

-- Create waf_services table
CREATE TABLE waf_services (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    gateway_name VARCHAR(255) NOT NULL,
    namespace VARCHAR(255) NOT NULL,
    region_code VARCHAR(50) NOT NULL,
    mode VARCHAR(50) NOT NULL,
    rule_num INTEGER DEFAULT 0,
    attack_num INTEGER DEFAULT 0,
    rule_category_status JSON,
);

-- Create waf_rules table
CREATE TABLE waf_rules (
    id SERIAL PRIMARY KEY,
    category_id VARCHAR(255) NOT NULL,
    level INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL,
    description TEXT,
    expr TEXT NOT NULL,
    mode VARCHAR(50) NOT NULL
);

-- Create waf_rule_categories table
CREATE TABLE waf_rule_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_id VARCHAR(255) NOT NULL,
    category_en VARCHAR(255) NOT NULL,
    category_zh VARCHAR(255) NOT NULL,
    description_en TEXT,
    description_zh TEXT,
    status INTEGER NOT NULL,
    rules JSON NOT NULL
);

-- Create gateway_listeners table
-- CREATE TABLE gateway_listeners (
--     id SERIAL PRIMARY KEY,
--     gateway_name VARCHAR(255) NOT NULL,
--     namespace VARCHAR(255) NOT NULL,
--     region_code VARCHAR(50) NOT NULL,
--     port INTEGER NOT NULL,
--     enable BOOLEAN NOT NULL,
--     hosts TEXT
-- );

CREATE TABLE `waf_listener_histories` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `name` varchar(255) DEFAULT NULL,
    `gateway_name` varchar(255) DEFAULT NULL,
    `listener_name` varchar(255) DEFAULT NULL,
    `namespace` varchar(255) DEFAULT NULL,
    `region_code` varchar(255) DEFAULT NULL,
    `description` text DEFAULT NULL,
    `status` int DEFAULT NULL,
    `operation` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS waf_blackwhitelists
(
    id               bigint       NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name             varchar(128) NOT NULL,
    scope            varchar(256) NOT NULL,
    mode             varchar(32),
    expr             varchar(512),
    status           smallint     NOT NULL,
    global           boolean              DEFAULT false,
    created_at       timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (name)
);

-- Add indexes for better query performance
CREATE INDEX idx_waf_services_gateway_name ON waf_services(gateway_name);
CREATE INDEX idx_waf_services_namespace ON waf_services(namespace);
CREATE INDEX idx_waf_rules_category_id ON waf_rules(category_id);
CREATE INDEX idx_waf_rule_categories_category_id ON waf_rule_categories(category_id);
CREATE INDEX idx_gateway_listeners_gateway_name ON gateway_listeners(gateway_name);
CREATE INDEX idx_gateway_listeners_namespace ON gateway_listeners(namespace);
CREATE INDEX idx_gateway_listeners_region_code ON gateway_listeners(region_code);