-- Missing Database Tables for CmdSharp Launch
-- Run this to create missing tables

-- Queue Requests Table
CREATE TABLE IF NOT EXISTS queue_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(255),
    request_type VARCHAR(100) NOT NULL,
    request_data JSON,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    priority INT DEFAULT 5,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    error_message TEXT,
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_priority (priority)
);

-- Branding Config Table
CREATE TABLE IF NOT EXISTS branding_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    logo_url VARCHAR(500),
    brand_name VARCHAR(255) DEFAULT 'CmdSharp',
    brand_tagline VARCHAR(500) DEFAULT 'Voice-Powered Invoicing System',
    primary_color VARCHAR(7) DEFAULT '#667eea',
    secondary_color VARCHAR(7) DEFAULT '#ffd700',
    success_redirect VARCHAR(500) DEFAULT 'https://cmdsharp.com/success.html',
    cancel_redirect VARCHAR(500) DEFAULT 'https://cmdsharp.com/cancel.html',
    download_page VARCHAR(500) DEFAULT 'https://cmdsharp.com/download',
    support_email VARCHAR(255) DEFAULT 'support@proviska.com',
    active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (active)
);

-- Insert default branding
INSERT INTO branding_config (
    logo_url, brand_name, brand_tagline, primary_color, secondary_color,
    success_redirect, cancel_redirect, download_page, support_email, active
) VALUES (
    'https://ai.proviska.com/assets/cmdsharp-logo.png',
    'CmdSharp',
    'Voice-Powered Invoicing System',
    '#667eea',
    '#ffd700',
    'https://cmdsharp.com/success.html',
    'https://cmdsharp.com/cancel.html',
    'https://cmdsharp.com/download',
    'support@proviska.com',
    1
) ON DUPLICATE KEY UPDATE id=id;

-- App Versions Table (for channel-specific APKs)
CREATE TABLE IF NOT EXISTS app_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_id VARCHAR(100) NOT NULL DEFAULT 'cmdsharp',
    version VARCHAR(50) NOT NULL,
    build_number INT NOT NULL,
    channel ENUM('direct', 'app_sumo', 'google_play') NOT NULL DEFAULT 'direct',
    apk_url VARCHAR(500),
    apk_size BIGINT,
    release_notes TEXT,
    is_active BOOLEAN DEFAULT 1,
    min_api_level INT DEFAULT 21,
    target_api_level INT DEFAULT 34,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_app_channel (app_id, channel),
    INDEX idx_active (is_active),
    UNIQUE KEY unique_version_channel (app_id, version, channel)
);

-- Cron Jobs Table (for proper cron management)
CREATE TABLE IF NOT EXISTS cron_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    command TEXT NOT NULL,
    schedule VARCHAR(100) NOT NULL, -- cron format: * * * * *
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    last_run TIMESTAMP NULL,
    last_status ENUM('success', 'failed', 'running') NULL,
    last_output TEXT,
    next_run TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_next_run (next_run)
);

-- Insert default cron jobs
INSERT INTO cron_jobs (name, command, schedule, description, is_active) VALUES 
('Queue Cleanup', 'php /var/www/html/proviska/proviska-ai/api/queue-admin.php cleanup', '0 */6 * * *', 'Clean old queue entries every 6 hours', 1),
('Health Check', 'php /var/www/html/proviska/proviska-ai/api/queue-health.php', '*/5 * * * *', 'Health check every 5 minutes', 1),
('Cache Cleanup', 'find /var/www/html/proviska/proviska-ai/cache -name "*.cache" -mtime +1 -delete', '0 2 * * *', 'Clean old cache files daily at 2am', 1)
ON DUPLICATE KEY UPDATE id=id;

-- System Status Table (for monitoring)
CREATE TABLE IF NOT EXISTS system_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    component VARCHAR(100) NOT NULL,
    status ENUM('healthy', 'warning', 'critical') NOT NULL,
    message TEXT,
    metrics JSON,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_component (component),
    INDEX idx_checked_at (checked_at)
);

-- Payments Table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id VARCHAR(255) UNIQUE NOT NULL,
    license_type VARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    provider VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    customer_email VARCHAR(255),
    customer_name VARCHAR(255),
    provider_transaction_id VARCHAR(255),
    webhook_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payment_id (payment_id),
    INDEX idx_status (status),
    INDEX idx_provider (provider),
    INDEX idx_created_at (created_at),
    INDEX idx_customer_email (customer_email)
);

-- Licenses Table
CREATE TABLE IF NOT EXISTS licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(255) UNIQUE NOT NULL,
    license_type VARCHAR(50) NOT NULL,
    channel VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    max_activations INT DEFAULT 1,
    current_activations INT DEFAULT 0,
    customer_email VARCHAR(255),
    customer_name VARCHAR(255),
    payment_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    activated_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    INDEX idx_license_key (license_key),
    INDEX idx_status (status),
    INDEX idx_channel (channel),
    INDEX idx_customer_email (customer_email),
    INDEX idx_payment_id (payment_id),
    FOREIGN KEY (payment_id) REFERENCES payments(payment_id) ON DELETE SET NULL
);
