-- AutoIt SaaS Licensing Database Schema

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'user') DEFAULT 'user',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `licenses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `software_name` VARCHAR(100) NOT NULL,
    `activation_key` VARCHAR(50) NOT NULL UNIQUE,
    `type` ENUM('trial', 'lifetime', 'subscription', 'usage_based') NOT NULL,
    `status` ENUM('active', 'expired', 'suspended') DEFAULT 'active',
    `max_usage` INT DEFAULT 0,
    `current_usage` INT DEFAULT 0,
    `expires_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS `hwid_locks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `license_id` INT NOT NULL,
    `hwid` VARCHAR(255) NOT NULL,
    `auth_token` VARCHAR(255) NOT NULL UNIQUE,
    `ip_address` VARCHAR(45) NOT NULL,
    `os_version` VARCHAR(100) DEFAULT NULL,
    `locked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`license_id`) REFERENCES `licenses`(`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `license_id` INT DEFAULT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `payment_gateway` VARCHAR(50) NOT NULL,
    `gateway_ref_id` VARCHAR(100) NOT NULL,
    `status` ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`license_id`) REFERENCES `licenses`(`id`) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS `app_versions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `version_number` VARCHAR(50) NOT NULL UNIQUE,
    `aes_key` VARCHAR(64) NOT NULL,
    `aes_iv` VARCHAR(32) NOT NULL,
    `download_url` VARCHAR(255) DEFAULT NULL,
    `file_hash` VARCHAR(64) DEFAULT NULL,
    `release_date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `activity_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `details` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
);

-- Insert a default admin user (Password: admin123)
-- In a production environment, you should change this immediately.
INSERT INTO `users` (`email`, `password_hash`, `role`) 
VALUES ('admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin')
ON DUPLICATE KEY UPDATE `email`=`email`;