-- ============================================
-- Affiliate Ninjas PRO License System Database
-- ============================================
-- Run this SQL in phpMyAdmin or your MySQL client
-- to create the required database structure

-- Create database (if needed)
-- CREATE DATABASE IF NOT EXISTS affiliateninjas_licenses;
-- USE affiliateninjas_licenses;

-- ============================================
-- Licenses Table
-- ============================================
CREATE TABLE IF NOT EXISTS `licenses` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_key` VARCHAR(50) NOT NULL,
    `customer_email` VARCHAR(255) NOT NULL,
    `customer_name` VARCHAR(255) DEFAULT NULL,
    `domain` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('active', 'inactive', 'expired', 'revoked') DEFAULT 'active',
    `license_type` ENUM('pro', 'developer', 'agency') DEFAULT 'pro',
    `max_activations` INT(11) DEFAULT 1,
    `current_activations` INT(11) DEFAULT 0,
    `order_id` VARCHAR(100) DEFAULT NULL COMMENT 'PayPal Transaction ID (txn_id)',
    `purchase_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATE DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `license_key` (`license_key`),
    KEY `customer_email` (`customer_email`),
    KEY `status` (`status`),
    KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Activations Table (tracks each domain activation)
-- ============================================
CREATE TABLE IF NOT EXISTS `activations` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_id` INT(11) UNSIGNED NOT NULL,
    `domain` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `activated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `last_checked` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `is_active` TINYINT(1) DEFAULT 1,
    PRIMARY KEY (`id`),
    KEY `license_id` (`license_id`),
    KEY `domain` (`domain`),
    CONSTRAINT `fk_activations_license` FOREIGN KEY (`license_id`) REFERENCES `licenses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- API Logs Table (optional - for tracking)
-- ============================================
CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `license_key` VARCHAR(50) DEFAULT NULL,
    `action` ENUM('validate', 'activate', 'deactivate', 'generate') NOT NULL,
    `domain` VARCHAR(255) DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `request_data` TEXT DEFAULT NULL,
    `response_data` TEXT DEFAULT NULL,
    `status` ENUM('success', 'failed') DEFAULT 'success',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `license_key` (`license_key`),
    KEY `action` (`action`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
