CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(36) NOT NULL,
  account_type ENUM('individual','organization') NOT NULL DEFAULT 'individual',
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('pending','active','suspended') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  company_name VARCHAR(180) NULL,
  tax_office VARCHAR(100) NULL,
  encrypted_identity TEXT NULL,
  address1 VARCHAR(250) NOT NULL DEFAULT '',
  address2 VARCHAR(250) NOT NULL DEFAULT '',
  district VARCHAR(100) NOT NULL DEFAULT '',
  province_code CHAR(2) NOT NULL DEFAULT '',
  postal_code VARCHAR(12) NOT NULL DEFAULT '',
  country_code CHAR(2) NOT NULL DEFAULT 'TR',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_public_id (public_id),
  UNIQUE KEY uq_customers_email (email),
  KEY idx_customers_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  purpose ENUM('verify_email','password_reset') NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_tokens_hash (token_hash),
  KEY idx_customer_tokens_customer (customer_id, purpose, expires_at),
  CONSTRAINT fk_customer_tokens_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_domains (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  domain_name VARCHAR(253) NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'METUnic',
  provider_service_id VARCHAR(120) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  registered_at DATETIME NULL,
  expires_at DATETIME NULL,
  nameservers TEXT NULL,
  auto_renew TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_domains_name (domain_name),
  KEY idx_customer_domains_customer_status (customer_id, status),
  KEY idx_customer_domains_expires (expires_at),
  CONSTRAINT fk_customer_domains_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS domain_renewal_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(36) NOT NULL,
  idempotency_key VARCHAR(80) NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  customer_domain_id BIGINT UNSIGNED NOT NULL,
  duration TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status VARCHAR(40) NOT NULL DEFAULT 'awaiting_payment',
  price DECIMAL(12,2) NULL,
  currency CHAR(3) NULL,
  provider_response TEXT NULL,
  error_message VARCHAR(500) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_domain_renewal_public_id (public_id),
  UNIQUE KEY uq_domain_renewal_idempotency (idempotency_key),
  KEY idx_domain_renewal_customer_status (customer_id, status, created_at),
  CONSTRAINT fk_domain_renewal_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_domain_renewal_domain FOREIGN KEY (customer_domain_id) REFERENCES customer_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS domain_claim_requests (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(36) NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  domain_name VARCHAR(253) NOT NULL,
  note VARCHAR(500) NOT NULL DEFAULT '',
  status VARCHAR(40) NOT NULL DEFAULT 'pending_review',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_domain_claim_public_id (public_id),
  KEY idx_domain_claim_customer_status (customer_id, status, created_at),
  CONSTRAINT fk_domain_claim_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL DEFAULT '',
  ip_address VARCHAR(45) NOT NULL DEFAULT '',
  successful TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_auth_attempts_ip_created (ip_address, created_at),
  KEY idx_auth_attempts_email_created (email, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;