M
CREATE TABLE IF NOT EXISTS supplier_performance ( supplier_id INTEGER REFERENCES suppliers(id) PRIMARY KEY, total_ignores INTEGER DEFAULT 0 CHECK (total_ignores >= 0), total_not_found INTEGER DEFAULT 0 CHECK (total_not_found >= 0), penalty_level INTEGER DEFAULT 0 CHECK (penalty_level >= 0 AND penalty_level last_penalty_date TIMESTAMP WITH TIME ZONE, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_performance_penalty ON supplier_performance(penalty_level) WHERE penalty_level > 0;CREATE TABLE IF NOT EXISTS fees_archive ( id BIGSERIAL PRIMARY KEY, customer_phone TEXT NOT NULL, request_id BIGINT REFERENCES customer_requests(id) ON DELETE SET NULL, amount NUMERIC(10, 2) DEFAULT 0 CHECK (amount >= 0), is_paid BOOLEAN DEFAULT FALSE, payment_date TIMESTAMP WITH TIME ZONE, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, paid_at TIMESTAMP WITH TIME ZONE);CREATE INDEX IF NOT EXISTS idx_fees_customer ON fees_archive(customer_phone);CREATE INDEX IF NOT EXISTS idx_fees_paid ON fees_archive(is_paid) WHERE is_paid = FALSE;CREATE INDEX IF NOT EXISTS idx_fees_request ON fees_archive(request_id);CREATE TABLE IF NOT EXISTS logs ( id BIGSERIAL PRIMARY KEY, phone_number TEXT, event_type TEXT NOT NULL CHECK (event_type IN ('message_received', 'message_sent', 'error', 'payment', 'penalty', 'system')), event_data JSONB DEFAULT '{}', node_name TEXT, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_logs_type ON logs(event_type);CREATE INDEX IF NOT EXISTS idx_logs_phone ON logs(phone_number);CREATE INDEX IF NOT EXISTS idx_logs_created ON logs(created_at);CREATE TABLE IF NOT EXISTS rate_limits ( phone_number TEXT PRIMARY KEY, message_count INTEGER DEFAULT 1 CHECK (message_count >= 0), window_start TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP);CREATE INDEX IF NOT EXISTS idx_rate_limits_window ON rate_limits(window_start);CREATE TABLE IF NOT EXISTS system_config ( key TEXT PRIMARY KEY, value TEXT NOT NULL, description TEXT, category TEXT DEFAULT 'general' CHECK (category IN ('general', 'whatsapp', 'telegram', 'fees', 'requests', 'work_hours', 'penalty', 'messages', 'security')), is_encrypted BOOLEAN DEFAULT FALSE, updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, updated_by TEXT);-- Default configINSERT INTO system_config (key, value, description, category) VALUES('whatsapp_api_version', 'v22.0', 'WhatsApp API version', 'whatsapp'),('whatsapp_phone_id', '', 'WhatsApp Phone ID', 'whatsapp'),('telegram_bot_token', '', 'Telegram Bot Token', 'telegram'),('telegram_admin_id', '', 'Telegram Admin ID', 'telegram'),('request_timeout_minutes', '2', 'Request timeout in minutes', 'requests'),('max_offers', '10', 'Maximum offers per request', 'requests'),('free_service_min_offers', '3', 'Minimum offers for free service', 'fees'),('service_fee', '9.63', 'Service fee amount', 'fees'),('service_fee_currency', 'ريال', 'Service fee currency', 'fees'),('enforce_work_hours', 'true', 'فرض ساعات العمل (تعطيله يسمح بالعمل 24 ساعة)', 'work_hours'),('work_start_hour', '7', 'Work start hour (24h)', 'work_hours'),('work_end_hour', '19', 'Work end hour (24h)', 'work_hours'),('timezone', 'Asia/Riyadh', 'System timezone', 'work_hours'),('penalty_threshold', '3', 'Penalty threshold (unified)', 'penalty'),('penalty_level_0_duration', '2 weeks', 'First penalty duration', 'penalty'),('penalty_level_1_duration', '1 month', 'Second penalty duration', 'penalty'),('penalty_level_2_duration', '1 year', 'Third penalty duration', 'penalty'),('rate_limit_window_minutes', '1', 'Rate limit window', 'security'),('rate_limit_max_requests', '20', 'Max requests per window', 'security')ON CONFLICT (key) DO NOTHING;
12.02.2026 01:12