-- ============================================================
-- SISTEMA DE LICENÇAS - MonitorPing
-- Execute este SQL no MySQL do cPanel (phpMyAdmin)
-- ============================================================

CREATE TABLE IF NOT EXISTS admins (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(150) NOT NULL,
    email         VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(150) NOT NULL,
    email      VARCHAR(150) NOT NULL UNIQUE,
    company    VARCHAR(150) DEFAULT NULL,
    phone      VARCHAR(30)  DEFAULT NULL,
    notes      TEXT         DEFAULT NULL,
    active     TINYINT(1)   NOT NULL DEFAULT 1,
    created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS licenses (
    id                       INT AUTO_INCREMENT PRIMARY KEY,
    license_key              VARCHAR(40)  NOT NULL UNIQUE,
    customer_id              INT          NOT NULL,
    -- segredo único por licença, usado para assinar chamadas HMAC
    api_secret               VARCHAR(64)  NOT NULL,
    hardware_id              VARCHAR(128) DEFAULT NULL,
    expires_at               DATE         NOT NULL,
    active                   TINYINT(1)   NOT NULL DEFAULT 1,
    notes                    TEXT         DEFAULT NULL,
    activated_at             DATETIME     DEFAULT NULL,
    last_check_at            DATETIME     DEFAULT NULL,
    -- token de instalação de uso único (expira em 48h)
    install_token            VARCHAR(64)  DEFAULT NULL,
    install_token_expires_at DATETIME     DEFAULT NULL,
    created_at               DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_license_key   (license_key),
    INDEX idx_customer_id   (customer_id),
    INDEX idx_install_token (install_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS license_logs (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(40)  NOT NULL,
    action      VARCHAR(30)  NOT NULL,
    hardware_id VARCHAR(128) DEFAULT NULL,
    ip_address  VARCHAR(45)  DEFAULT NULL,
    message     VARCHAR(255) DEFAULT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_license_key (license_key),
    INDEX idx_created_at  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Migração para banco existente ────────────────────────────
-- (seguro rodar mesmo se as colunas já existirem)
ALTER TABLE licenses
    ADD COLUMN IF NOT EXISTS api_secret               VARCHAR(64)  NOT NULL DEFAULT '',
    ADD COLUMN IF NOT EXISTS install_token            VARCHAR(64)  DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS install_token_expires_at DATETIME     DEFAULT NULL;

-- Gera api_secret para licenças que ainda não têm
UPDATE licenses SET api_secret = SHA2(CONCAT(license_key, RAND(), UUID()), 256) WHERE api_secret = '';
