-- Users table for web interface authentication (must be created first due to foreign key)
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY COMMENT 'Random 7-digit unique ID generated by application',
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    fullname VARCHAR(255) NULL,
    google_id VARCHAR(255) NULL UNIQUE,
    password_hash VARCHAR(255) NULL,
    api_key VARCHAR(64) UNIQUE NULL,
    is_admin BOOLEAN DEFAULT FALSE,
    is_banned BOOLEAN DEFAULT FALSE,
    banned_at TIMESTAMP NULL,
    tier VARCHAR(50) DEFAULT NULL,
    tier_expires_at DATETIME NULL,
    billing_cycle_start DATE NULL COMMENT 'When current billing period started (for usage reset)',
    billing_cycle_end DATE NULL COMMENT 'When current billing period ends (for usage reset)',
    phone_number VARCHAR(20) NULL COMMENT 'User phone number with country code (e.g., +923001234567)',
    phone_verified BOOLEAN DEFAULT FALSE COMMENT 'Whether phone number is verified',
    phone_verified_at TIMESTAMP NULL COMMENT 'When phone was verified',
    phone_country_code VARCHAR(5) NULL COMMENT 'Country code (e.g., +92 for Pakistan)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_google_id (google_id),
    INDEX idx_api_key (api_key),
    INDEX idx_tier (tier),
    INDEX idx_is_banned (is_banned),
    INDEX idx_billing_cycle_end (billing_cycle_end),
    INDEX idx_phone_number (phone_number),
    INDEX idx_phone_verified (phone_verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Users table with Google OAuth and phone verification support';

-- Sessions table to store WhatsApp session information
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id INT NULL,
    name VARCHAR(255) NOT NULL,
    status ENUM('connecting', 'connected', 'disconnected', 'qr') DEFAULT 'disconnected',
    qr_code TEXT,
    phone_number VARCHAR(20),
    session_data LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_seen TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Auto-replies table for automated message responses
CREATE TABLE IF NOT EXISTS auto_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    trigger_type ENUM('exact', 'contains', 'starts_with', 'ends_with', 'regex') DEFAULT 'contains',
    trigger_value TEXT NOT NULL,
    reply_messages JSON NOT NULL,
    reply_to_self BOOLEAN DEFAULT FALSE,
    share_code VARCHAR(12) UNIQUE NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_is_active (is_active),
    INDEX idx_share_code (share_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Assistant configuration table for AI integration (OpenAI, DeepSeek, Gemini, OpenRouter)
CREATE TABLE IF NOT EXISTS ai_assistants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL UNIQUE,
    ai_provider ENUM('openai', 'deepseek', 'gemini', 'openrouter') DEFAULT 'openai',
    ai_api_key VARCHAR(255) NOT NULL,
    knowledge_base TEXT NULL,
    system_prompt TEXT NULL,
    model VARCHAR(50) DEFAULT 'gpt-4o-mini',
    temperature DECIMAL(2,1) DEFAULT 0.7,
    max_tokens INT DEFAULT 500,
    conversation_limit INT DEFAULT 10 COMMENT 'Number of previous messages to remember (0 = no memory)',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session_id (session_id),
    INDEX idx_is_active (is_active),
    INDEX idx_ai_provider (ai_provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Conversation history table for AI context memory
CREATE TABLE IF NOT EXISTS conversation_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    user_jid VARCHAR(255) NOT NULL COMMENT 'WhatsApp user ID (phone number)',
    role ENUM('user', 'assistant') NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    INDEX idx_session_user (session_id, user_jid),
    INDEX idx_created_at (created_at),
    INDEX idx_session_user_time (session_id, user_jid, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores conversation history for AI context';

-- Message templates table for saving reusable messages
CREATE TABLE IF NOT EXISTS message_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    message TEXT NULL,
    media_path VARCHAR(500) NULL,
    media_type ENUM('text', 'image', 'video', 'document', 'audio', 'sticker', 'location', 'contact', 'poll', 'viewOnceImage', 'viewOnceVideo', 'viewOnceAudio') DEFAULT 'text',
    template_data JSON NULL COMMENT 'Stores additional data for advanced message types (location coords, contact info, poll options, etc.)',
    is_favorite BOOLEAN DEFAULT FALSE,
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_media_type (media_type),
    INDEX idx_is_favorite (is_favorite)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contact groups table
CREATE TABLE IF NOT EXISTS contact_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    color VARCHAR(7) DEFAULT '#25D366' COMMENT 'Hex color code',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    UNIQUE KEY unique_user_group (user_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Contact groups for organization';

-- Contacts table for managing WhatsApp contacts
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    group_id INT NULL,
    name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    is_favorite BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_group_id (group_id),
    INDEX idx_phone_number (phone_number),
    INDEX idx_name (name),
    INDEX idx_is_favorite (is_favorite),
    UNIQUE KEY unique_group_phone (group_id, phone_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores contact information for users - same number allowed in different groups';

-- Campaigns table for bulk messaging
CREATE TABLE IF NOT EXISTS campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    session_ids JSON NOT NULL COMMENT 'Array of session IDs for multi-session support',
    method ENUM('sequential', 'random', 'robin', 'balanced', 'burst') DEFAULT 'sequential',
    contacts JSON NOT NULL,
    message TEXT,
    media_url VARCHAR(500),
    message_type VARCHAR(50) DEFAULT 'text' COMMENT 'Message type: text, media, sticker, location, contact, poll, viewOnceImage, viewOnceVideo, viewOnceAudio',
    message_data JSON COMMENT 'Additional data for advanced message types (location coords, contact info, poll options, etc.)',
    delay INT DEFAULT 3 COMMENT 'Delay between messages in seconds',
    scheduled_at DATETIME,
    status ENUM('pending', 'running', 'completed', 'failed', 'paused') DEFAULT 'pending',
    is_paused BOOLEAN DEFAULT FALSE COMMENT 'Real-time pause state synced from memory',
    sent_count INT DEFAULT 0 COMMENT 'Number of successfully sent messages',
    failed_count INT DEFAULT 0 COMMENT 'Number of failed messages',
    pending_count INT DEFAULT 0 COMMENT 'Number of pending messages',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_campaign_status_counts (status, sent_count, failed_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add message_type and message_data columns if they don't exist (for existing databases)
-- Run these ALTER statements manually if upgrading:
-- ALTER TABLE campaigns ADD COLUMN message_type VARCHAR(50) DEFAULT 'text' AFTER media_url;
-- ALTER TABLE campaigns ADD COLUMN message_data JSON AFTER message_type;
-- ALTER TABLE campaigns MODIFY COLUMN message TEXT;

-- Campaign logs table for tracking message delivery
CREATE TABLE IF NOT EXISTS campaign_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NULL COMMENT 'NULL for API messages',
    user_id INT NOT NULL COMMENT 'User who sent the message',
    contact_id INT,
    phone_number VARCHAR(20) NOT NULL,
    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
    error TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at),
    INDEX idx_user_month (user_id, sent_at),
    INDEX idx_user_status_sent (user_id, status, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Logs preserved when campaign deleted to maintain accurate message count';

-- Usage logs table for tracking user activity and tier limits
CREATE TABLE IF NOT EXISTS usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    count INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_date (user_id, created_at),
    INDEX idx_action_type (action_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plans table for managing subscription tiers
CREATE TABLE IF NOT EXISTS plans (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'PKR',
    expiry_days INT NULL COMMENT 'NULL = lifetime',
    total_messages INT NOT NULL DEFAULT 0 COMMENT 'Total messages for subscription period (-1 = unlimited)',
    total_sessions INT NOT NULL DEFAULT 0 COMMENT '-1 = unlimited',
    total_contacts INT NOT NULL DEFAULT 0 COMMENT '-1 = unlimited',
    total_templates INT NOT NULL DEFAULT 0 COMMENT '-1 = unlimited',
    total_number_checkers INT NOT NULL DEFAULT 0 COMMENT '-1 = unlimited, limit for total contacts checked',
    api_requests_per_hour INT NOT NULL DEFAULT 0 COMMENT 'API requests allowed per hour (-1 = unlimited, 0 = no API access)',
    feature_ai_assistant BOOLEAN DEFAULT FALSE,
    feature_auto_reply BOOLEAN DEFAULT TRUE,
    feature_api_access BOOLEAN DEFAULT FALSE,
    is_popular BOOLEAN DEFAULT FALSE,
    is_default BOOLEAN DEFAULT FALSE COMMENT 'Only one plan can be default (free tier for downgrades)',
    color VARCHAR(7) DEFAULT '#667eea' COMMENT 'Hex color code for plan display',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_is_active (is_active),
    INDEX idx_is_default (is_default),
    INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default plans
INSERT INTO plans (id, name, price, expiry_days, total_messages, total_sessions, total_contacts, total_templates, total_number_checkers, api_requests_per_hour, feature_ai_assistant, feature_auto_reply, feature_api_access, is_popular, is_default) VALUES
('free', 'Free', 0, NULL, 3000, 2, 100, 10, 100, 50, FALSE, TRUE, FALSE, FALSE, TRUE),
('starter', 'Starter', 5000, 30, 30000, 10, 1000, 50, 1000, 200, TRUE, TRUE, FALSE, FALSE, FALSE),
('professional', 'Professional', 12000, 30, 300000, 50, 10000, 200, 10000, 1000, TRUE, TRUE, TRUE, TRUE, FALSE),
('business', 'Business', 35000, 30, 1500000, 200, 100000, -1, 100000, 5000, TRUE, TRUE, TRUE, FALSE, FALSE),
('enterprise', 'Enterprise', 0, NULL, -1, -1, -1, -1, -1, -1, TRUE, TRUE, TRUE, FALSE, FALSE)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    price = VALUES(price),
    expiry_days = VALUES(expiry_days),
    total_messages = VALUES(total_messages),
    total_sessions = VALUES(total_sessions),
    total_contacts = VALUES(total_contacts),
    total_templates = VALUES(total_templates),
    total_number_checkers = VALUES(total_number_checkers),
    api_requests_per_hour = VALUES(api_requests_per_hour),
    feature_ai_assistant = VALUES(feature_ai_assistant),
    feature_auto_reply = VALUES(feature_auto_reply),
    feature_api_access = VALUES(feature_api_access),
    is_popular = VALUES(is_popular),
    is_default = VALUES(is_default);

-- API request logs table for tracking API usage and rate limiting
CREATE TABLE IF NOT EXISTS api_request_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    status_code INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at),
    INDEX idx_created_at (created_at),
    INDEX idx_user_hour_lookup (user_id, created_at DESC),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- OTP verification table for phone number verification
CREATE TABLE IF NOT EXISTS phone_otp_verifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    otp_code VARCHAR(6) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    verified BOOLEAN DEFAULT FALSE,
    attempts INT DEFAULT 0 COMMENT 'Number of verification attempts',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_phone_number (phone_number),
    INDEX idx_expires_at (expires_at),
    INDEX idx_verified (verified)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Temporary storage for phone OTP verifications';

-- Number checkers table for validating WhatsApp numbers
CREATE TABLE IF NOT EXISTS number_checkers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    session_id VARCHAR(255) NOT NULL,
    group_id INT NULL,
    status ENUM('pending', 'running', 'completed', 'failed', 'paused') DEFAULT 'pending',
    is_paused BOOLEAN DEFAULT FALSE,
    check_interval INT DEFAULT 300 COMMENT 'Delay between checks in milliseconds',
    total_contacts INT DEFAULT 0,
    checked_contacts INT DEFAULT 0,
    valid_contacts INT DEFAULT 0,
    invalid_contacts INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Number checker logs table for tracking validation results
CREATE TABLE IF NOT EXISTS number_checker_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    checker_id INT NULL,
    user_id INT NULL,
    contact_id INT NULL,
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(255) NULL,
    status ENUM('pending', 'valid', 'invalid', 'error') DEFAULT 'pending',
    jid VARCHAR(255) NULL,
    error TEXT NULL,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (checker_id) REFERENCES number_checkers(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    INDEX idx_checker_id (checker_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Logs preserved when checker deleted to maintain accurate limit tracking';

-- ============================================
-- Insert Default Admin User
-- ============================================
-- Email: admin@admin.com
-- Password: admin123
-- ⚠️ IMPORTANT: Change password after first login!

INSERT INTO users (
    id,
    username, 
    email, 
    fullname,
    password_hash,
    is_admin,
    tier,
    created_at
) VALUES (
    1000000, -- Fixed admin ID
    'admin',
    'admin@admin.com',
    'Administrator',
    '$2a$10$bjq.9weaQHA7U3lQF1CglOy7tAl6Kz2uhqjdFv3MlJdZrm6AatyRO', -- Password: admin123
    TRUE,
    'free',
    NOW()
) ON DUPLICATE KEY UPDATE
    password_hash = VALUES(password_hash);

-- NOTE: Admin user will be created when you import this SQL file
-- Login credentials:
--   Email: admin@admin.com
--   Password: admin123