CREATE TABLE IF NOT EXISTS customers (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  email      VARCHAR(190) NOT NULL UNIQUE,
  phone      VARCHAR(40),
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS licenses (
  id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id         INT UNSIGNED NOT NULL,
  license_key         VARCHAR(40) NOT NULL UNIQUE,
  plan                VARCHAR(30) NOT NULL DEFAULT 'basico',
  status              ENUM('active','past_due','cancelled','suspended') NOT NULL DEFAULT 'active',
  max_activations     TINYINT UNSIGNED NOT NULL DEFAULT 1,
  current_period_end  DATE NOT NULL,
  grace_days          TINYINT UNSIGNED NOT NULL DEFAULT 5,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activations (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_id   INT UNSIGNED NOT NULL,
  hwid         CHAR(64) NOT NULL,
  device_name  VARCHAR(120),
  first_seen   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_ip      VARCHAR(45),
  active       TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (license_id) REFERENCES licenses(id),
  UNIQUE KEY uniq_license_hwid (license_id, hwid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS validation_log (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_key VARCHAR(40) NOT NULL,
  hwid        CHAR(64),
  ip          VARCHAR(45),
  endpoint    VARCHAR(20),
  result      VARCHAR(20),
  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;
