

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

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

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

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