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

-- ============================================
-- Admin Users Table (for admin panel access)
-- ============================================
CREATE TABLE IF NOT EXISTS `admin_users` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Insert default admin user
-- Default: admin / changeme123 (CHANGE THIS!)
-- ============================================
INSERT INTO `admin_users` (`username`, `password_hash`, `email`) VALUES
('admin', '$2y$10$YourHashedPasswordHere', 'your@email.com');

-- Note: Generate a proper password hash using:
-- php -r "echo password_hash('your_secure_password', PASSWORD_DEFAULT);"

-- ============================================
-- PayPal Purchases Table
-- Used by IPN listener and thank-you page
-- ============================================
CREATE TABLE IF NOT EXISTS `paypal_purchases` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `txn_id` VARCHAR(100) NOT NULL COMMENT 'PayPal Transaction ID',
    `custom_token` VARCHAR(100) DEFAULT NULL COMMENT 'Token from payment button custom field',
    `license_key` VARCHAR(50) DEFAULT NULL,
    `license_id` INT(11) UNSIGNED DEFAULT NULL,
    `customer_email` VARCHAR(255) NOT NULL,
    `customer_name` VARCHAR(255) DEFAULT NULL,
    `license_type` ENUM('pro', 'developer', 'agency') DEFAULT 'pro',
    `amount` DECIMAL(10,2) DEFAULT NULL,
    `currency` VARCHAR(10) DEFAULT 'USD',
    `payment_status` VARCHAR(50) DEFAULT 'pending',
    `raw_ipn` TEXT DEFAULT NULL COMMENT 'Full raw IPN data for debugging',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `txn_id` (`txn_id`),
    KEY `custom_token` (`custom_token`),
    KEY `customer_email` (`customer_email`),
    KEY `license_key` (`license_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;