-- Billing: currencies, products with custom fields, CWP servers, services, orders, invoices,
-- transactions, gateways and the module log. Ids follow WHMCS so an import keeps them.

CREATE TABLE currencies (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code CHAR(3) NOT NULL,
  prefix VARCHAR(10) NOT NULL DEFAULT '',
  suffix VARCHAR(10) NOT NULL DEFAULT '',
  rate DECIMAL(12,5) NOT NULL DEFAULT 1,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  type VARCHAR(16) NOT NULL DEFAULT 'hosting' COMMENT 'hosting|license|service|other',
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  pay_type VARCHAR(10) NOT NULL DEFAULT 'recurring' COMMENT 'free|onetime|recurring',
  module VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'provisioning module, e.g. cwp; empty = manual',
  module_config TEXT NULL COMMENT 'JSON, e.g. {"package":"default","autossl":1}',
  server_group_id INT UNSIGNED NULL,
  auto_setup VARCHAR(10) NOT NULL DEFAULT 'payment' COMMENT 'order|payment|manual',
  require_domain TINYINT(1) NOT NULL DEFAULT 0,
  stock INT NULL COMMENT 'NULL = unlimited',
  hidden TINYINT(1) NOT NULL DEFAULT 0,
  retired TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE product_prices (
  product_id INT UNSIGNED NOT NULL,
  currency_id INT UNSIGNED NOT NULL,
  cycle VARCHAR(16) NOT NULL COMMENT 'onetime|monthly|quarterly|semiannually|annually|biennially|triennially',
  price DECIMAL(16,2) NOT NULL,
  setup_fee DECIMAL(16,2) NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, currency_id, cycle)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Custom fields per product (server IP, SSH port, root password ...). secret = stored only in the vault.
CREATE TABLE product_fields (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  type VARCHAR(16) NOT NULL DEFAULT 'text' COMMENT 'text|textarea|number|select|checkbox|password',
  options TEXT NULL COMMENT 'select: one option per line',
  validation VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'optional regex',
  required TINYINT(1) NOT NULL DEFAULT 0,
  show_on_order TINYINT(1) NOT NULL DEFAULT 1,
  client_visible TINYINT(1) NOT NULL DEFAULT 1,
  client_editable TINYINT(1) NOT NULL DEFAULT 1,
  secret TINYINT(1) NOT NULL DEFAULT 0,
  view_perm VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'admin permission needed to see the value; empty = services.view',
  sort INT NOT NULL DEFAULT 0,
  KEY product (product_id, sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE server_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  fill VARCHAR(8) NOT NULL DEFAULT 'least' COMMENT 'least = server with fewest accounts, fill = first with room'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE servers (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NULL,
  name VARCHAR(100) NOT NULL,
  hostname VARCHAR(190) NOT NULL,
  ip VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'IP for new accounts (server_ips)',
  module VARCHAR(32) NOT NULL DEFAULT 'cwp',
  port INT UNSIGNED NOT NULL DEFAULT 2304,
  verify_ssl TINYINT(1) NOT NULL DEFAULT 1,
  api_key_enc TEXT NULL COMMENT 'encrypted with the app key',
  nameservers VARCHAR(500) NOT NULL DEFAULT '',
  max_accounts INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = no limit',
  disabled TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'Pending' COMMENT 'Pending|Active|Cancelled|Fraud',
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  invoice_id INT UNSIGNED NULL,
  payment_method VARCHAR(32) NOT NULL DEFAULT '',
  ip VARCHAR(64) NOT NULL DEFAULT '',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  KEY client (client_id),
  KEY status (status, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A product a client has (WHMCS tblhosting).
CREATE TABLE services (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  order_id INT UNSIGNED NULL,
  product_id INT UNSIGNED NOT NULL,
  server_id INT UNSIGNED NULL,
  domain VARCHAR(190) NOT NULL DEFAULT '',
  username VARCHAR(64) NOT NULL DEFAULT '',
  password_enc TEXT NULL COMMENT 'hosting account password, encrypted with the app key',
  status VARCHAR(16) NOT NULL DEFAULT 'Pending' COMMENT 'Pending|Active|Suspended|Terminated|Cancelled|Fraud',
  billing_cycle VARCHAR(16) NOT NULL DEFAULT 'monthly',
  first_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0 COMMENT 'recurring amount',
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  reg_date DATE NOT NULL,
  next_due_date DATE NULL,
  termination_date DATE NULL,
  suspend_reason VARCHAR(255) NOT NULL DEFAULT '',
  no_auto_suspend TINYINT(1) NOT NULL DEFAULT 0,
  no_auto_suspend_until DATE NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY client (client_id, status),
  KEY due (status, next_due_date),
  KEY server (server_id),
  KEY domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE service_fields (
  service_id INT UNSIGNED NOT NULL,
  field_id INT UNSIGNED NOT NULL,
  value TEXT NOT NULL,
  PRIMARY KEY (service_id, field_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  number VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'sequential number, set when the invoice is issued',
  status VARCHAR(16) NOT NULL DEFAULT 'Unpaid' COMMENT 'Draft|Unpaid|Paid|Cancelled|Refunded|Collections',
  date DATE NOT NULL,
  due_date DATE NOT NULL,
  paid_at DATETIME NULL,
  subtotal DECIMAL(16,2) NOT NULL DEFAULT 0,
  credit DECIMAL(16,2) NOT NULL DEFAULT 0,
  tax_name VARCHAR(32) NOT NULL DEFAULT '',
  tax_rate DECIMAL(6,3) NOT NULL DEFAULT 0,
  tax DECIMAL(16,2) NOT NULL DEFAULT 0,
  total DECIMAL(16,2) NOT NULL DEFAULT 0,
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  payment_method VARCHAR(32) NOT NULL DEFAULT '',
  notes TEXT NULL,
  last_reminder_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY client (client_id, status),
  KEY due (status, due_date),
  KEY number (number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoice_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT UNSIGNED NOT NULL,
  client_id INT UNSIGNED NOT NULL,
  service_id INT UNSIGNED NULL,
  type VARCHAR(16) NOT NULL DEFAULT 'service' COMMENT 'service|setup|manual|late_fee',
  description TEXT NOT NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  taxed TINYINT(1) NOT NULL DEFAULT 1,
  period_start DATE NULL,
  period_end DATE NULL,
  KEY invoice (invoice_id),
  KEY service (service_id, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE transactions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  invoice_id INT UNSIGNED NULL,
  gateway VARCHAR(32) NOT NULL,
  transaction_id VARCHAR(190) NOT NULL,
  amount DECIMAL(16,2) NOT NULL COMMENT 'negative = refund',
  fee DECIMAL(16,2) NOT NULL DEFAULT 0,
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  description VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  UNIQUE KEY gw_tx (gateway, transaction_id),
  KEY invoice (invoice_id),
  KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE gateways (
  code VARCHAR(32) NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  settings_enc TEXT NULL COMMENT 'JSON encrypted with the app key (API keys, secrets)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Raw payment notifications (webhooks/IPN) for debugging; secrets are not stored.
CREATE TABLE gateway_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  gateway VARCHAR(32) NOT NULL,
  result VARCHAR(32) NOT NULL,
  data MEDIUMTEXT NOT NULL,
  created_at DATETIME NOT NULL,
  KEY gw (gateway, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE credit_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  amount DECIMAL(16,2) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  invoice_id INT UNSIGNED NULL,
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every call to a server (create, suspend ...), passwords and API keys stripped.
CREATE TABLE module_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  service_id INT UNSIGNED NULL,
  server_id INT UNSIGNED NULL,
  action VARCHAR(32) NOT NULL,
  request TEXT NOT NULL,
  response TEXT NOT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  KEY service (service_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vault secrets can also belong to a service field (long-lived), not only to a ticket.
ALTER TABLE vault_secrets MODIFY ticket_id INT UNSIGNED NULL, MODIFY expires_at DATETIME NULL,
  ADD service_id INT UNSIGNED NULL AFTER ticket_id, ADD field_id INT UNSIGNED NULL AFTER service_id, ADD KEY service (service_id, field_id);
ALTER TABLE vault_access MODIFY ticket_id INT UNSIGNED NULL;

INSERT INTO currencies (id, code, prefix, suffix, rate, is_default) VALUES (1, 'USD', '$', ' USD', 1, 1);

INSERT INTO gateways (code, name, enabled, sort) VALUES
 ('stripe', 'Credit card (Stripe)', 0, 1),
 ('paypal', 'PayPal', 0, 2),
 ('skrill', 'Skrill', 0, 3),
 ('banktransfer', 'Bank transfer', 0, 4);

UPDATE admin_roles SET perms = '["tickets.view","tickets.reply","tickets.notes","clients.view","clients.edit","services.view","orders.manage","invoices.view","invoices.manage"]' WHERE id = 3;
UPDATE admin_roles SET perms = '["tickets.view","tickets.reply","tickets.notes","tickets.manage","clients.view","vault.reveal","services.view"]' WHERE id = 2;
UPDATE admin_roles SET perms = '["tickets.view","tickets.reply","tickets.notes","tickets.manage","vault.reveal","services.view"]' WHERE id = 4;