-- MySQL Database Setup for License Management System

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


CREATE TABLE IF NOT EXISTS licenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_code VARCHAR(100) NOT NULL UNIQUE,
    plan_slug VARCHAR(50) NOT NULL DEFAULT 'pro',
    owner_type VARCHAR(50) NOT NULL DEFAULT 'customer',
    duration_days INT NOT NULL DEFAULT 30,
    status ENUM('active', 'revoked', 'expired') NOT NULL DEFAULT 'active',
    max_devices INT NOT NULL DEFAULT 1,
    device_id VARCHAR(255) DEFAULT NULL,
    customer_name VARCHAR(255) DEFAULT NULL,
    customer_contact VARCHAR(255) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    activated_at DATETIME DEFAULT NULL,
    expires_at DATETIME DEFAULT NULL,
    last_seen_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX(key_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS license_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_id INT DEFAULT NULL,
    key_code VARCHAR(100) DEFAULT NULL,
    event_type VARCHAR(50) NOT NULL,
    result VARCHAR(50) DEFAULT NULL,
    device_id VARCHAR(255) DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent TEXT DEFAULT NULL,
    detail JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Default admin user (password: admin123)
-- In a real setup, the user should change this immediately.
INSERT IGNORE INTO users (username, password) VALUES ('admin', '$2y$10$QjSHW9lb/AzsK9H1gjMQ5.bZJbNlA1J.p7gE8zS9r.QxQp7s8s9.2'); -- admin123 hash