create_waf_tables.sql

-- Create waf_services table
CREATE TABLE waf_services (
    id SERIAL PRIMARY KEY,
    gateway_name VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL,
    namespace VARCHAR(255) NOT NULL,
    region_code VARCHAR(50) NOT NULL,
    host TEXT NOT NULL,  -- Stored as comma-separated string
    mode VARCHAR(50) NOT NULL,
    rule_num INTEGER DEFAULT 0,
    attack_num INTEGER DEFAULT 0,
    rule_category_status JSONB NOT NULL
);

-- 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 SERIAL PRIMARY KEY,
    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 JSONB NOT NULL
);

-- 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);