-- WZAuth 2.0 - Migração incremental
-- Seguro para executar sobre o banco atual: NÃO apaga customers, customer_ips, auth_logs ou admins.

CREATE TABLE IF NOT EXISTS customer_profiles (
    customer_id INT UNSIGNED NOT NULL PRIMARY KEY,
    contact_name VARCHAR(120) NOT NULL DEFAULT '',
    email VARCHAR(160) NOT NULL DEFAULT '',
    phone VARCHAR(40) NOT NULL DEFAULT '',
    notes TEXT NULL,
    portal_username VARCHAR(160) NULL,
    portal_password_hash VARCHAR(255) NULL,
    portal_status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    last_portal_login_at DATETIME NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_customer_profiles_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    UNIQUE KEY uq_customer_profiles_portal_username (portal_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL DEFAULT '',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO products (code,name,description,status) VALUES
('CLIENTE','CLIENTE','Cliente de jogo e launcher','active'),
('MUSERVER','MUSERVER','Servidor de jogo','active'),
('TOOLS','TOOLS','Ferramentas e utilitários','active');

CREATE TABLE IF NOT EXISTS licenses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_code VARCHAR(40) NOT NULL UNIQUE,
    customer_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    customer_name VARCHAR(100) NOT NULL UNIQUE,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    max_ips SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    expires_at DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_license_customer (customer_id),
    INDEX idx_license_product (product_id),
    INDEX idx_license_status (status),
    CONSTRAINT fk_license_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT fk_license_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_ips (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id INT UNSIGNED NOT NULL,
    ip VARCHAR(45) NOT NULL,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_license_ip (license_id, ip),
    INDEX idx_license_ips_ip (ip),
    CONSTRAINT fk_license_ips_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_versions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    version VARCHAR(50) NOT NULL,
    build VARCHAR(50) NOT NULL DEFAULT '',
    changelog TEXT NULL,
    status ENUM('published','beta','draft','archived') NOT NULL DEFAULT 'published',
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_product_version (product_id, version),
    INDEX idx_versions_status (status),
    CONSTRAINT fk_version_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_files (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_version_id INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    mime_type VARCHAR(120) NOT NULL DEFAULT 'application/octet-stream',
    sha256 CHAR(64) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_files_version (product_version_id),
    CONSTRAINT fk_file_version FOREIGN KEY (product_version_id) REFERENCES product_versions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS download_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NULL,
    license_id INT UNSIGNED NULL,
    product_version_id INT UNSIGNED NULL,
    file_id INT UNSIGNED NULL,
    ip VARCHAR(45) NOT NULL DEFAULT '',
    result ENUM('success','denied','failed') NOT NULL DEFAULT 'success',
    reason VARCHAR(120) NOT NULL DEFAULT 'ok',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_download_created (created_at),
    INDEX idx_download_customer (customer_id),
    INDEX idx_download_license (license_id),
    CONSTRAINT fk_download_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    CONSTRAINT fk_download_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL,
    CONSTRAINT fk_download_version FOREIGN KEY (product_version_id) REFERENCES product_versions(id) ON DELETE SET NULL,
    CONSTRAINT fk_download_file FOREIGN KEY (file_id) REFERENCES product_files(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS license_change_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    license_id INT UNSIGNED NOT NULL,
    admin_id INT UNSIGNED NULL,
    change_type VARCHAR(60) NOT NULL,
    details VARCHAR(500) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_change_license (license_id),
    INDEX idx_change_created (created_at),
    CONSTRAINT fk_change_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    CONSTRAINT fk_change_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;