-- MySQL-compatible certificates repository and audit log

CREATE TABLE IF NOT EXISTS certificates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(190) NOT NULL,
  cnpj VARCHAR(20) NOT NULL,
  type VARCHAR(3) NOT NULL,
  environment VARCHAR(16) NOT NULL,
  subject VARCHAR(255),
  issuer VARCHAR(255),
  serial_number VARCHAR(128),
  thumbprint_sha1 VARCHAR(40),
  valid_from DATETIME NULL,
  valid_to DATETIME NULL,
  payload_enc LONGBLOB NOT NULL,
  payload_iv VARBINARY(32) NOT NULL,
  payload_tag VARBINARY(16) NOT NULL,
  kdf_salt VARBINARY(32) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_certificates_tenant (tenant_id),
  INDEX idx_certificates_cnpj (cnpj),
  INDEX idx_certificates_env (environment)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS certificate_audits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id VARCHAR(190) NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  certificate_id BIGINT UNSIGNED NULL,
  action VARCHAR(64) NOT NULL,
  ip VARCHAR(64),
  user_agent VARCHAR(255),
  success TINYINT(1) NOT NULL DEFAULT 1,
  message TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cert_audits_tenant (tenant_id),
  INDEX idx_cert_audits_cert (certificate_id),
  INDEX idx_cert_audits_action (action),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;