-- domain names: TLD pricing, registrar modules, registered / transferred domains, renewals on invoices
CREATE TABLE domain_tlds (
  tld VARCHAR(63) NOT NULL PRIMARY KEY COMMENT 'without the leading dot, e.g. com, co.uk',
  registrar VARCHAR(32) NOT NULL DEFAULT 'manual',
  epp_required TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'transfers need an EPP/auth code',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE domain_prices (
  tld VARCHAR(63) NOT NULL,
  currency_id INT UNSIGNED NOT NULL,
  years TINYINT UNSIGNED NOT NULL,
  register DECIMAL(16,2) NULL COMMENT 'NULL = not offered',
  transfer DECIMAL(16,2) NULL,
  renew DECIMAL(16,2) NULL,
  PRIMARY KEY (tld, currency_id, years)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE registrars (
  code VARCHAR(32) NOT NULL PRIMARY KEY,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  settings_enc TEXT NULL COMMENT 'JSON, encrypted with the app key',
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE domains (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NULL,
  domain VARCHAR(253) NOT NULL,
  registrar VARCHAR(32) NOT NULL DEFAULT 'manual',
  type VARCHAR(10) NOT NULL DEFAULT 'register' COMMENT 'register|transfer',
  status VARCHAR(20) NOT NULL DEFAULT 'Pending' COMMENT 'Pending|Pending Transfer|Active|Expired|Cancelled|Transferred Away|Fraud',
  years TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT 'renewal period',
  first_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'renewal price for the period',
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  payment_method VARCHAR(32) NOT NULL DEFAULT '',
  reg_date DATE NOT NULL,
  expiry_date DATE NULL,
  next_due_date DATE NULL,
  auto_renew TINYINT(1) NOT NULL DEFAULT 1,
  nameservers VARCHAR(600) NOT NULL DEFAULT '' COMMENT 'comma separated',
  id_protection TINYINT(1) NOT NULL DEFAULT 0,
  epp_enc TEXT NULL COMMENT 'transfer auth code until the transfer is started, encrypted',
  notes TEXT NULL,
  synced_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY client (client_id),
  KEY domain (domain),
  KEY due (status, next_due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE invoice_items ADD domain_id INT UNSIGNED NULL AFTER addon_id, ADD KEY domain (domain_id, period_start);
INSERT IGNORE INTO registrars (code, enabled, updated_at) VALUES ('manual', 1, NOW());
INSERT IGNORE INTO settings (k, v) VALUES ('domain_renew_days', '30'), ('domain_auto_register', '1'), ('domain_default_ns', '');