-- cmdsharp Multi-Channel Licensing Database Schema

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS proviska_ai;
USE proviska_ai;

-- Payments table for tracking all payment attempts
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id VARCHAR(255) UNIQUE NOT NULL,
    license_type ENUM('single', 'team') NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) NOT NULL,
    provider ENUM('payfast', 'lemonsqueezy') NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'cancelled') NOT NULL,
    customer_email VARCHAR(255) NULL,
    customer_name VARCHAR(255) NULL,
    provider_transaction_id VARCHAR(255) NULL,
    webhook_data JSON NULL,
    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_created_at (created_at)
);

-- Licenses table for generated licenses
CREATE TABLE IF NOT EXISTS licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(255) UNIQUE NOT NULL,
    license_type ENUM('single', 'team') NOT NULL,
    channel ENUM('app_sumo', 'direct', 'google_play') NOT NULL,
    status ENUM('active', 'inactive', 'expired', 'suspended') NOT NULL DEFAULT 'active',
    max_activations INT NOT NULL DEFAULT 1,
    current_activations INT NOT NULL DEFAULT 0,
    customer_email VARCHAR(255) NULL,
    customer_name VARCHAR(255) NULL,
    payment_id VARCHAR(255) NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_license_key (license_key),
    INDEX idx_channel (channel),
    INDEX idx_status (status),
    INDEX idx_customer_email (customer_email),
    FOREIGN KEY (payment_id) REFERENCES payments(payment_id) ON DELETE SET NULL
);

-- License activations table for tracking device activations
CREATE TABLE IF NOT EXISTS license_activations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(255) NOT NULL,
    device_id VARCHAR(255) NOT NULL,
    device_name VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    app_version VARCHAR(50) NULL,
    activated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_license_key (license_key),
    INDEX idx_device_id (device_id),
    INDEX idx_activated_at (activated_at),
    FOREIGN KEY (license_key) REFERENCES licenses(license_key) ON DELETE CASCADE,
    UNIQUE KEY unique_license_device (license_key, device_id)
);

-- Activity logs for tracking all system activities
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    details JSON NULL,
    user_id INT NULL,
    license_key VARCHAR(255) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_action (action),
    INDEX idx_created_at (created_at),
    INDEX idx_license_key (license_key)
);

-- AppSumo codes table (for existing AppSumo integration)
CREATE TABLE IF NOT EXISTS appsumo_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(255) UNIQUE NOT NULL,
    license_key VARCHAR(255) NULL,
    redeemed_at TIMESTAMP NULL,
    redeemed_by_email VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_license_key (license_key),
    FOREIGN KEY (license_key) REFERENCES licenses(license_key) ON DELETE SET NULL
);

-- Email templates table
CREATE TABLE IF NOT EXISTS email_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) UNIQUE NOT NULL,
    channel ENUM('app_sumo', 'direct', 'google_play', 'all') NOT NULL DEFAULT 'all',
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_template_name (template_name),
    INDEX idx_channel (channel)
);

-- Insert default email templates
INSERT INTO email_templates (template_name, channel, subject, body) VALUES 
('license_purchased', 'direct', 'Your cmdsharp License Key', 
'<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial, sans-serif; color: #333; }
        .header { background: #304c67; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; }
        .license-key { background: #f8f9fa; padding: 15px; border-radius: 5px; font-family: monospace; font-size: 18px; text-align: center; margin: 20px 0; border: 2px solid #304c67; }
        .footer { background: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="header">
        <h1>Welcome to cmdsharp!</h1>
    </div>
    <div class="content">
        <h2>Thank you for your purchase!</h2>
        <p>Your cmdsharp license has been successfully created. Here are your details:</p>
        
        <div class="license-key">
            <strong>{{LICENSE_KEY}}</strong>
        </div>
        
        <p><strong>License Type:</strong> {{LICENSE_TYPE}}</p>
        <p><strong>Valid For:</strong> {{MAX_ACTIVATIONS}} device(s)</p>
        
        <h3>How to activate:</h3>
        <ol>
            <li>Download cmdsharp from <a href="https://cmdsharp.com/download">cmdsharp.com</a></li>
            <li>Open the app and enter your license key</li>
            <li>Start commanding your invoicing!</li>
        </ol>
        
        <p>Need help? Contact us at <a href="mailto:support@cmdsharp.com">support@cmdsharp.com</a></p>
    </div>
    <div class="footer">
        <p>© 2024 cmdsharp. All rights reserved.</p>
    </div>
</body>
</html>'),

('license_activated', 'all', 'cmdsharp License Activated', 
'<!DOCTYPE html>
<html>
<head>
    <style>
        body { font-family: Arial, sans-serif; color: #333; }
        .header { background: #304c67; color: white; padding: 20px; text-align: center; }
        .content { padding: 20px; }
        .success { background: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin: 20px 0; }
        .footer { background: #f8f9fa; padding: 20px; text-align: center; font-size: 12px; color: #666; }
    </style>
</head>
<body>
    <div class="header">
        <h1>License Activated!</h1>
    </div>
    <div class="content">
        <div class="success">
            <strong>✅ Success!</strong> Your cmdsharp license has been activated on this device.
        </div>
        
        <p>Your license details:</p>
        <ul>
            <li><strong>License Key:</strong> {{LICENSE_KEY}}</li>
            <li><strong>Device:</strong> {{DEVICE_NAME}}</li>
            <li><strong>Activated:</strong> {{ACTIVATED_AT}}</li>
        </ul>
        
        <p>You can now enjoy all cmdsharp features including:</p>
        <ul>
            <li>Voice-powered invoice creation</li>
            <li>AI magic shortcuts</li>
            <li>Programmable templates</li>
            <li>Export and reporting</li>
        </ul>
        
        <p>Happy invoicing!</p>
    </div>
    <div class="footer">
        <p>© 2024 cmdsharp. All rights reserved.</p>
    </div>
</body>
</html>')
ON DUPLICATE KEY UPDATE id=id;

-- Create indexes for better performance
CREATE INDEX idx_payments_provider_status ON payments(provider, status);
CREATE INDEX idx_licenses_channel_status ON licenses(channel, status);
CREATE INDEX idx_activations_license_last_seen ON license_activations(license_key, last_seen);
CREATE INDEX idx_logs_action_created ON activity_logs(action, created_at);
