-- Add Automation Tables to Existing proviska_ai Database
-- This script adds ONLY the new automation tables to the existing database
-- without touching any existing tables or data.
--
-- Usage on production server:
-- mysql -u root -p proviska_ai < /var/www/html/proviska-ai/scripts/add-automation-tables.sql

USE proviska_ai;

-- ===================================================================
-- AUTOMATION FAQS - Unified FAQ system across all channels
-- ===================================================================
CREATE TABLE IF NOT EXISTS automation_faqs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    
    -- FAQ content
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    keywords TEXT, -- comma-separated for quick matching
    
    -- Channel applicability
    chat_enabled BOOLEAN DEFAULT TRUE,
    email_enabled BOOLEAN DEFAULT TRUE,
    reviews_enabled BOOLEAN DEFAULT FALSE,
    
    -- FAQ management
    category VARCHAR(100) DEFAULT 'general',
    priority INT DEFAULT 5, -- 1=highest, 10=lowest
    confidence_threshold DECIMAL(3,2) DEFAULT 0.80,
    
    -- Usage analytics
    usage_count INT DEFAULT 0,
    last_used TIMESTAMP NULL,
    success_rate DECIMAL(3,2) DEFAULT 1.00,
    
    -- Source type for integration
    source_type ENUM('chat', 'email', 'review', 'manual') DEFAULT 'manual',
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (business_id) REFERENCES automation_businesses(id) ON DELETE CASCADE,
    INDEX idx_business_id (business_id),
    INDEX idx_category (category),
    INDEX idx_active (is_active),
    INDEX idx_priority (priority),
    INDEX idx_source_type (source_type),
    FULLTEXT idx_question_keywords (question, keywords)
);

-- ===================================================================
-- AUTOMATION MESSAGES - Unified message storage
-- ===================================================================
CREATE TABLE IF NOT EXISTS automation_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    
    -- Message source
    source_type ENUM('chat', 'email', 'review') NOT NULL,
    source_id VARCHAR(255), -- conversation_id, email_id, or review_id
    
    -- Message content
    message_text TEXT NOT NULL,
    sender_type ENUM('customer', 'ai', 'human') NOT NULL,
    sender_identifier VARCHAR(255), -- email, name, or system
    
    -- AI processing
    ai_processed BOOLEAN DEFAULT FALSE,
    ai_confidence DECIMAL(3,2),
    ai_response_type ENUM('faq', 'generated', 'escalated') NULL,
    faq_matched_id INT NULL, -- References automation_faqs.id
    
    -- Response tracking
    response_text TEXT,
    response_sent BOOLEAN DEFAULT FALSE,
    response_method VARCHAR(50), -- email, whatsapp, chat
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    
    FOREIGN KEY (business_id) REFERENCES automation_businesses(id) ON DELETE CASCADE,
    FOREIGN KEY (faq_matched_id) REFERENCES automation_faqs(id) ON DELETE SET NULL,
    INDEX idx_business_id (business_id),
    INDEX idx_source_type (source_type),
    INDEX idx_source_id (source_id),
    INDEX idx_sender_type (sender_type),
    INDEX idx_ai_processed (ai_processed),
    INDEX idx_created_at (created_at)
);

-- ===================================================================
-- WHATSAPP NOTIFICATIONS - WhatsApp integration
-- ===================================================================
CREATE TABLE IF NOT EXISTS whatsapp_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    
    -- Notification details
    recipient_number VARCHAR(20) NOT NULL,
    message_text TEXT NOT NULL,
    message_type ENUM('lead_alert', 'response_notification', 'system_alert') NOT NULL,
    
    -- Trigger information
    triggered_by VARCHAR(100), -- 'new_lead', 'faq_match', 'system_event'
    reference_id INT, -- ID of the triggering record
    reference_type VARCHAR(50), -- 'chat_conversation', 'automation_message'
    
    -- Processing status
    status ENUM('pending', 'sent', 'failed', 'blocked') DEFAULT 'pending',
    sent_at TIMESTAMP NULL,
    error_message TEXT,
    
    -- Rate limiting
    retry_count INT DEFAULT 0,
    max_retries INT DEFAULT 3,
    next_retry_at TIMESTAMP NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (business_id) REFERENCES automation_businesses(id) ON DELETE CASCADE,
    INDEX idx_business_id (business_id),
    INDEX idx_status (status),
    INDEX idx_message_type (message_type),
    INDEX idx_created_at (created_at),
    INDEX idx_next_retry_at (next_retry_at)
);

-- ===================================================================
-- AUTOMATION ANALYTICS - Daily aggregated analytics
-- ===================================================================
CREATE TABLE IF NOT EXISTS automation_analytics_daily (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT NOT NULL,
    analytics_date DATE NOT NULL,
    
    -- Chat metrics
    chat_conversations INT DEFAULT 0,
    chat_messages INT DEFAULT 0,
    chat_leads_generated INT DEFAULT 0,
    chat_ai_responses INT DEFAULT 0,
    
    -- Email metrics
    emails_processed INT DEFAULT 0,
    emails_replied INT DEFAULT 0,
    email_leads_generated INT DEFAULT 0,
    
    -- Review metrics
    reviews_processed INT DEFAULT 0,
    review_responses_generated INT DEFAULT 0,
    
    -- AI performance metrics
    total_ai_requests INT DEFAULT 0,
    ai_response_accuracy DECIMAL(3,2) DEFAULT 0.00,
    faq_matches INT DEFAULT 0,
    generated_responses INT DEFAULT 0,
    
    -- WhatsApp metrics
    whatsapp_notifications_sent INT DEFAULT 0,
    
    -- Lead metrics
    total_leads_generated INT DEFAULT 0,
    lead_conversion_rate DECIMAL(3,2) DEFAULT 0.00,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (business_id) REFERENCES automation_businesses(id) ON DELETE CASCADE,
    INDEX idx_business_id (business_id),
    INDEX idx_analytics_date (analytics_date),
    UNIQUE KEY unique_business_date (business_id, analytics_date)
);

-- ===================================================================
-- AUTOMATION CONFIG - System configuration per business
-- ===================================================================
CREATE TABLE IF NOT EXISTS automation_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_id INT DEFAULT NULL, -- NULL means global config
    
    config_key VARCHAR(100) NOT NULL,
    config_value TEXT,
    config_type ENUM('string', 'integer', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE, -- Can be exposed to frontend
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (business_id) REFERENCES automation_businesses(id) ON DELETE CASCADE,
    INDEX idx_business_id (business_id),
    INDEX idx_config_key (config_key),
    INDEX idx_is_public (is_public),
    UNIQUE KEY unique_business_config (business_id, config_key)
);

-- Login Attempts - Track failed logins for security
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    business_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_email_time (email, attempt_time),
    INDEX idx_business_time (business_id, attempt_time),
    INDEX idx_ip_time (ip_address, attempt_time)
);

-- Authentication Logs - Audit trail for all auth events
CREATE TABLE IF NOT EXISTS authentication_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    business_id INT,
    action ENUM('login_success', 'login_failed', 'logout', 'password_reset', 'mfa_enabled', 'mfa_disabled', 'account_locked', 'account_unlocked') NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    additional_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_business_id (business_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- Role Permissions - Define what each role can do
CREATE TABLE IF NOT EXISTS role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role VARCHAR(50) NOT NULL,
    resource VARCHAR(100) NOT NULL,
    action VARCHAR(50) NOT NULL,
    allowed BOOLEAN DEFAULT TRUE,
    
    UNIQUE KEY unique_role_permission (role, resource, action),
    INDEX idx_role (role)
);

-- Insert default role permissions
INSERT INTO role_permissions (role, resource, action) VALUES 
-- Owner can do everything
('owner', 'dashboard', 'view'), ('owner', 'dashboard', 'edit'),
('owner', 'faqs', 'create'), ('owner', 'faqs', 'edit'), ('owner', 'faqs', 'delete'),
('owner', 'users', 'create'), ('owner', 'users', 'edit'), ('owner', 'users', 'delete'),
('owner', 'settings', 'view'), ('owner', 'settings', 'edit'),
('owner', 'billing', 'view'), ('owner', 'billing', 'edit'),
('owner', 'api_keys', 'view'), ('owner', 'api_keys', 'regenerate'),

-- Admin can manage everything except billing and users
('admin', 'dashboard', 'view'), ('admin', 'dashboard', 'edit'),
('admin', 'faqs', 'create'), ('admin', 'faqs', 'edit'), ('admin', 'faqs', 'delete'),
('admin', 'settings', 'view'), ('admin', 'settings', 'edit'),
('admin', 'api_keys', 'view'),

-- Manager can view and edit content
('manager', 'dashboard', 'view'), ('manager', 'dashboard', 'edit'),
('manager', 'faqs', 'create'), ('manager', 'faqs', 'edit'),
('manager', 'settings', 'view'),

-- Viewer can only view
('viewer', 'dashboard', 'view'),
('viewer', 'faqs', 'view'),
('viewer', 'settings', 'view')
ON DUPLICATE KEY UPDATE allowed=VALUES(allowed);

-- Sample FAQ entries
INSERT INTO automation_faqs (business_id, question, answer, keywords, category, priority) VALUES
(@business_id, 'What are your business hours?', 'Our business hours are Monday to Friday, 9:00 AM to 5:00 PM SAST.', 'hours,time,open,closed,monday,friday', 'general', 1),
(@business_id, 'How can I contact you?', 'You can contact us via email at info@acmeconsulting.co.za or call us at +27 11 123 4567.', 'contact,email,phone,call', 'contact', 1),
(@business_id, 'What services do you offer?', 'We offer business consulting, strategy development, and process optimization services.', 'services,consulting,strategy,optimization', 'services', 2)
ON DUPLICATE KEY UPDATE id=id;

-- Global configuration settings
INSERT INTO automation_config (business_id, config_key, config_value, config_type, description, is_public) VALUES 
(NULL, 'system_version', '1.0.0', 'string', 'Automation platform version', TRUE),
(NULL, 'ai_service_url', 'https://yourdomain.com/proviska-ai/api', 'string', 'Base URL for AI service endpoints', FALSE),
(NULL, 'default_timezone', 'Africa/Johannesburg', 'string', 'Default system timezone', TRUE),
(NULL, 'max_chat_sessions_per_hour', '100', 'integer', 'Rate limit for chat sessions per business', FALSE),
(NULL, 'whatsapp_rate_limit_per_day', '1000', 'integer', 'WhatsApp messages per business per day', FALSE)
ON DUPLICATE KEY UPDATE config_value=VALUES(config_value);

-- ===================================================================
-- SUCCESS MESSAGE
-- ===================================================================
SELECT 'Automation tables added successfully to proviska_ai database!' AS result;
