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

CREATE TABLE admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','blocked') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL UNIQUE,
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_ips (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_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,
    UNIQUE KEY uq_customer_ip (customer_id, ip),
    CONSTRAINT fk_customer_ips_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auth_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    ip VARCHAR(45) NOT NULL,
    machine_id VARCHAR(255) NOT NULL DEFAULT '',
    app_version VARCHAR(50) NOT NULL DEFAULT '',
    result ENUM('authorized','denied') NOT NULL,
    reason VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs_customer (customer_name),
    INDEX idx_logs_ip (ip),
    INDEX idx_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- 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','suspended','blocked') NOT NULL DEFAULT 'active',
    max_ips SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    expires_at DATE NULL,
    suspended_until DATE NULL,
    suspension_reason VARCHAR(255) NOT NULL DEFAULT '',
    renewed_at DATETIME NULL,
    renewal_count INT UNSIGNED NOT NULL DEFAULT 0,
    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),
    INDEX idx_license_expiry (expires_at, 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;

-- Gere o hash com password_hash('SUA_SENHA', PASSWORD_DEFAULT) e substitua o valor abaixo.
INSERT INTO admins (username,password_hash)
VALUES ('admin','$2y$10$REPLACE_WITH_A_REAL_PASSWORD_HASH');


-- WZAuth Painel do Cliente V1
-- Execute APÓS sql/migration_admin_v1.sql.
-- Não apaga nenhuma tabela ou dado existente.

CREATE TABLE IF NOT EXISTS portal_activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    license_id INT UNSIGNED NULL,
    action VARCHAR(60) NOT NULL,
    details VARCHAR(500) NOT NULL DEFAULT '',
    ip VARCHAR(45) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_portal_customer (customer_id),
    INDEX idx_portal_license (license_id),
    INDEX idx_portal_created (created_at),
    CONSTRAINT fk_portal_activity_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT fk_portal_activity_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- WZAuth V1.2 - Estado operacional das licenças
CREATE TABLE IF NOT EXISTS license_runtime_state (
    license_id INT UNSIGNED NOT NULL PRIMARY KEY,
    machine_id VARCHAR(255) NOT NULL DEFAULT '',
    app_version VARCHAR(50) NOT NULL DEFAULT '',
    last_ip VARCHAR(45) NOT NULL DEFAULT '',
    last_seen_at DATETIME NULL,
    auth_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_runtime_seen (last_seen_at),
    INDEX idx_runtime_version (app_version),
    CONSTRAINT fk_runtime_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- WZAuth V1.3 - Gestão do ciclo de vida
-- Para bases existentes, execute sql/migration_v1_3.sql.

-- WZAuth V1.4 - Renovação paga
CREATE TABLE IF NOT EXISTS renewal_plans (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NULL,
    months TINYINT UNSIGNED NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    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,
    INDEX idx_renewal_plan_product (product_id),
    INDEX idx_renewal_plan_months (months,status),
    CONSTRAINT fk_renewal_plan_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 renewal_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(64) NOT NULL UNIQUE,
    license_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    renewal_plan_id INT UNSIGNED NULL,
    months TINYINT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    provider VARCHAR(40) NOT NULL DEFAULT 'mercado_pago',
    provider_order_id VARCHAR(100) NULL UNIQUE,
    provider_status VARCHAR(40) NOT NULL DEFAULT 'created',
    provider_status_detail VARCHAR(80) NOT NULL DEFAULT '',
    status ENUM('pending','paid','failed','canceled','refunded') NOT NULL DEFAULT 'pending',
    checkout_url TEXT NULL,
    idempotency_key VARCHAR(128) NOT NULL UNIQUE,
    old_expires_at DATE NULL,
    new_expires_at DATE NULL,
    paid_at DATETIME NULL,
    applied_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_renewal_order_license (license_id),
    INDEX idx_renewal_order_customer (customer_id),
    INDEX idx_renewal_order_status (status,created_at),
    CONSTRAINT fk_renewal_order_license FOREIGN KEY (license_id) REFERENCES licenses(id) ON DELETE CASCADE,
    CONSTRAINT fk_renewal_order_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    CONSTRAINT fk_renewal_order_plan FOREIGN KEY (renewal_plan_id) REFERENCES renewal_plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_webhook_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(40) NOT NULL,
    event_id VARCHAR(100) NOT NULL DEFAULT '',
    event_type VARCHAR(80) NOT NULL DEFAULT '',
    provider_resource_id VARCHAR(120) NOT NULL DEFAULT '',
    signature_valid TINYINT(1) NOT NULL DEFAULT 0,
    http_status SMALLINT UNSIGNED NOT NULL DEFAULT 200,
    payload MEDIUMTEXT NULL,
    processed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_webhook_resource (provider_resource_id),
    INDEX idx_webhook_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;