-- LicenseForge Database Schema
-- Run this once to set up the database

CREATE DATABASE IF NOT EXISTS licenseforge CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE licenseforge;

-- Admin users (you, the SaaS owner)
CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Your customers (resellers / buyers)
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMP NULL
);

-- Software products you sell licences for
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,
  version VARCHAR(50) DEFAULT '1.0.0',
  max_activations INT DEFAULT 1,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- User access to products (admin grants this)
CREATE TABLE IF NOT EXISTS user_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  max_keys INT DEFAULT 5,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_product (user_id, product_id)
);

-- Licence keys generated by users
CREATE TABLE IF NOT EXISTS licences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  licence_key VARCHAR(64) UNIQUE NOT NULL,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  max_activations INT DEFAULT 1,
  is_active TINYINT(1) DEFAULT 1,
  notes VARCHAR(500) DEFAULT '',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expires_at TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Individual machine activations of a licence key
CREATE TABLE IF NOT EXISTS activations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  licence_id INT NOT NULL,
  machine_id VARCHAR(255) NOT NULL,
  machine_name VARCHAR(255) DEFAULT 'Unknown',
  ip_address VARCHAR(45) DEFAULT '',
  activated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  is_active TINYINT(1) DEFAULT 1,
  FOREIGN KEY (licence_id) REFERENCES licences(id) ON DELETE CASCADE,
  UNIQUE KEY unique_licence_machine (licence_id, machine_id)
);

-- API request log for debugging / abuse detection
CREATE TABLE IF NOT EXISTS api_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  endpoint VARCHAR(255),
  licence_key VARCHAR(64),
  machine_id VARCHAR(255),
  ip_address VARCHAR(45),
  response_code VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default admin account (change password immediately!)
-- Password: admin123
INSERT IGNORE INTO admins (email, password_hash)
VALUES ('admin@licenseforge.com', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- Example product
INSERT IGNORE INTO products (name, slug, version, max_activations)
VALUES ('WhatsApp Sales Manager', 'whatsapp-sales-manager', '1.0.0', 2);