CREATE DATABASE IF NOT EXISTS iqmedya
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE iqmedya;

CREATE TABLE IF NOT EXISTS contact_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(40) NOT NULL DEFAULT '',
  service VARCHAR(80) NOT NULL DEFAULT '',
  message TEXT NOT NULL,
  ip_address VARCHAR(45) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_contact_created_at (created_at),
  KEY idx_contact_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS domain_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(36) NOT NULL,
  idempotency_key VARCHAR(80) NOT NULL,
  domain_name VARCHAR(253) NOT NULL,
  extension VARCHAR(20) NOT NULL,
  duration TINYINT UNSIGNED NOT NULL DEFAULT 1,
  registrant_type ENUM('individual','organization') NOT NULL,
  customer_name VARCHAR(160) NOT NULL,
  customer_email VARCHAR(190) NOT NULL,
  customer_phone VARCHAR(40) NOT NULL,
  encrypted_payload LONGTEXT NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'METUnic',
  status VARCHAR(40) NOT NULL DEFAULT 'awaiting_payment',
  provider_service_id VARCHAR(120) NULL,
  price DECIMAL(12,2) NULL,
  currency CHAR(3) NULL,
  provider_response TEXT NULL,
  error_message VARCHAR(500) NULL,
  ip_address VARCHAR(45) NOT NULL DEFAULT '',
  consent_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_domain_orders_public_id (public_id),
  UNIQUE KEY uq_domain_orders_idempotency (idempotency_key),
  KEY idx_domain_orders_domain (domain_name),
  KEY idx_domain_orders_status_created (status, created_at),
  KEY idx_domain_orders_ip_created (ip_address, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;