-- Enterprise License Management System Database Schema

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `product_slug` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT NULL,
    `version` VARCHAR(20) DEFAULT '1.0.0',
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `default_activation_limit` INT DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `licenses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `license_key` VARCHAR(100) NOT NULL UNIQUE,
    `customer_name` VARCHAR(100) NOT NULL,
    `customer_email` VARCHAR(100) NOT NULL,
    `customer_phone` VARCHAR(30) NULL,
    `order_id` VARCHAR(50) NULL,
    `license_type` ENUM('lifetime', 'fixed') DEFAULT 'fixed',
    `activation_limit` INT DEFAULT 1,
    `status` ENUM('active', 'inactive', 'expired', 'revoked', 'suspended') DEFAULT 'active',
    `update_access` TINYINT(1) DEFAULT 1,
    `expires_at` DATETIME NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `license_activations` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `license_id` INT NOT NULL,
    `domain` VARCHAR(255) NOT NULL,
    `site_url` VARCHAR(255) NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `plugin_version` VARCHAR(20) NULL,
    `activated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `last_verified_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_active` TINYINT(1) DEFAULT 1,
    FOREIGN KEY (`license_id`) REFERENCES `licenses`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_domain_license` (`license_id`, `domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `api_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `endpoint` VARCHAR(100) NOT NULL,
    `license_key` VARCHAR(100) NULL,
    `domain` VARCHAR(255) NULL,
    `response_code` INT NOT NULL,
    `status` VARCHAR(50) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `action` VARCHAR(100) NOT NULL,
    `details` TEXT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;