<?php
/**
 * Database schema definition.
 *
 * @package HostKhata
 */

namespace HostKhata\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Holds the CREATE TABLE statements for every HostKhata table.
 *
 * The statements are plain strings built from a prefix and a collation, so the
 * class has no WordPress dependency and can be unit tested. They follow the
 * formatting rules of dbDelta(): one column per line, two spaces after
 * PRIMARY KEY, KEY instead of INDEX.
 *
 * @since 0.1.0
 */
final class Schema {

	/**
	 * Short names of all tables, in creation order.
	 *
	 * @since 0.1.0
	 *
	 * @return string[]
	 */
	public static function keys(): array {
		return array(
			'customers',
			'customer_contacts',
			'customer_notes',
			'product_groups',
			'products',
			'product_prices',
			'orders',
			'order_items',
			'services',
			'domains',
			'invoices',
			'invoice_items',
			'payments',
			'credits',
			'coupons',
			'tax_rates',
			'notices',
			'notice_reads',
			'reminder_rules',
			'reminder_log',
			'email_queue',
			'email_log',
			'activity_log',
		);
	}

	/**
	 * Builds the CREATE TABLE statements.
	 *
	 * @since 0.1.0
	 *
	 * @param string $prefix  Table prefix, normally `$wpdb->prefix`.
	 * @param string $collate Charset and collation clause, normally `$wpdb->get_charset_collate()`.
	 * @return array<string, string> Statement per table key.
	 */
	public static function statements( string $prefix, string $collate ): array {
		$t = $prefix . 'hostkhata_';

		return array(
			'customers'         => "CREATE TABLE {$t}customers (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL,
  first_name varchar(100) NOT NULL DEFAULT '',
  last_name varchar(100) NOT NULL DEFAULT '',
  company varchar(190) NOT NULL DEFAULT '',
  phone varchar(40) NOT NULL DEFAULT '',
  address_1 varchar(190) NOT NULL DEFAULT '',
  address_2 varchar(190) NOT NULL DEFAULT '',
  city varchar(100) NOT NULL DEFAULT '',
  state varchar(100) NOT NULL DEFAULT '',
  postcode varchar(20) NOT NULL DEFAULT '',
  country char(2) NOT NULL DEFAULT '',
  status varchar(20) NOT NULL DEFAULT 'active',
  credit_balance bigint(20) NOT NULL DEFAULT 0,
  tags varchar(500) NOT NULL DEFAULT '',
  reminders_muted tinyint(1) NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY user_id (user_id),
  KEY status (status)
) {$collate};",

			'customer_contacts' => "CREATE TABLE {$t}customer_contacts (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  customer_id bigint(20) unsigned NOT NULL,
  name varchar(190) NOT NULL DEFAULT '',
  email varchar(190) NOT NULL DEFAULT '',
  phone varchar(40) NOT NULL DEFAULT '',
  job_role varchar(60) NOT NULL DEFAULT '',
  is_primary tinyint(1) NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY customer_id (customer_id)
) {$collate};",

			'customer_notes'    => "CREATE TABLE {$t}customer_notes (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  customer_id bigint(20) unsigned NOT NULL,
  author_id bigint(20) unsigned NOT NULL DEFAULT 0,
  visibility varchar(20) NOT NULL DEFAULT 'internal',
  note longtext NOT NULL,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY customer_id (customer_id)
) {$collate};",

			'product_groups'    => "CREATE TABLE {$t}product_groups (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(190) NOT NULL,
  slug varchar(190) NOT NULL,
  description text NOT NULL,
  sort_order int(11) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'active',
  PRIMARY KEY  (id),
  UNIQUE KEY slug (slug)
) {$collate};",

			'products'          => "CREATE TABLE {$t}products (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  group_id bigint(20) unsigned NOT NULL DEFAULT 0,
  type varchar(20) NOT NULL DEFAULT 'hosting',
  name varchar(190) NOT NULL,
  slug varchar(190) NOT NULL,
  description longtext NOT NULL,
  features longtext NULL,
  custom_fields longtext NULL,
  addon_ids longtext NULL,
  tax_class varchar(40) NOT NULL DEFAULT '',
  stock_limit int(11) DEFAULT NULL,
  is_popular tinyint(1) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'active',
  sort_order int(11) NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY slug (slug),
  KEY group_id (group_id),
  KEY type_status (type,status)
) {$collate};",

			'product_prices'    => "CREATE TABLE {$t}product_prices (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  product_id bigint(20) unsigned NOT NULL,
  price_type varchar(20) NOT NULL DEFAULT 'recurring',
  billing_cycle varchar(20) NOT NULL DEFAULT 'monthly',
  period_months int(11) NOT NULL DEFAULT 1,
  price bigint(20) NOT NULL DEFAULT 0,
  setup_fee bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  KEY product_id (product_id)
) {$collate};",

			'orders'            => "CREATE TABLE {$t}orders (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  order_number varchar(40) NOT NULL,
  customer_id bigint(20) unsigned NOT NULL,
  status varchar(30) NOT NULL DEFAULT 'pending',
  subtotal bigint(20) NOT NULL DEFAULT 0,
  discount_total bigint(20) NOT NULL DEFAULT 0,
  tax_total bigint(20) NOT NULL DEFAULT 0,
  total bigint(20) NOT NULL DEFAULT 0,
  coupon_id bigint(20) unsigned NOT NULL DEFAULT 0,
  coupon_code varchar(60) NOT NULL DEFAULT '',
  payment_method varchar(40) NOT NULL DEFAULT '',
  ip_address varchar(45) NOT NULL DEFAULT '',
  customer_notes text NULL,
  admin_notes text NULL,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  paid_at_gmt datetime DEFAULT NULL,
  fulfilled_at_gmt datetime DEFAULT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY order_number (order_number),
  KEY customer_id (customer_id),
  KEY status (status)
) {$collate};",

			'order_items'       => "CREATE TABLE {$t}order_items (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  order_id bigint(20) unsigned NOT NULL,
  product_id bigint(20) unsigned NOT NULL DEFAULT 0,
  item_type varchar(20) NOT NULL DEFAULT 'product',
  description varchar(500) NOT NULL DEFAULT '',
  billing_cycle varchar(20) NOT NULL DEFAULT '',
  quantity int(11) NOT NULL DEFAULT 1,
  unit_price bigint(20) NOT NULL DEFAULT 0,
  setup_fee bigint(20) NOT NULL DEFAULT 0,
  tax_amount bigint(20) NOT NULL DEFAULT 0,
  total bigint(20) NOT NULL DEFAULT 0,
  domain_name varchar(253) NOT NULL DEFAULT '',
  meta longtext NULL,
  PRIMARY KEY  (id),
  KEY order_id (order_id),
  KEY product_id (product_id)
) {$collate};",

			'services'          => "CREATE TABLE {$t}services (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  customer_id bigint(20) unsigned NOT NULL,
  order_id bigint(20) unsigned NOT NULL DEFAULT 0,
  order_item_id bigint(20) unsigned NOT NULL DEFAULT 0,
  product_id bigint(20) unsigned NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'pending',
  domain varchar(253) NOT NULL DEFAULT '',
  username varchar(100) NOT NULL DEFAULT '',
  server_label varchar(100) NOT NULL DEFAULT '',
  server_ip varchar(45) NOT NULL DEFAULT '',
  panel_url varchar(255) NOT NULL DEFAULT '',
  billing_cycle varchar(20) NOT NULL DEFAULT 'monthly',
  recurring_amount bigint(20) NOT NULL DEFAULT 0,
  start_date date DEFAULT NULL,
  next_due_date date DEFAULT NULL,
  end_date date DEFAULT NULL,
  notes longtext NULL,
  meta longtext NULL,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY customer_id (customer_id),
  KEY order_id (order_id),
  KEY status_due (status,next_due_date)
) {$collate};",

			'domains'           => "CREATE TABLE {$t}domains (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  customer_id bigint(20) unsigned NOT NULL,
  order_id bigint(20) unsigned NOT NULL DEFAULT 0,
  service_id bigint(20) unsigned NOT NULL DEFAULT 0,
  domain_name varchar(253) NOT NULL,
  tld varchar(63) NOT NULL DEFAULT '',
  registrar varchar(100) NOT NULL DEFAULT '',
  status varchar(20) NOT NULL DEFAULT 'pending',
  registered_on date DEFAULT NULL,
  expires_on date DEFAULT NULL,
  renewal_years int(11) NOT NULL DEFAULT 1,
  auto_renew tinyint(1) NOT NULL DEFAULT 0,
  whois_privacy tinyint(1) NOT NULL DEFAULT 0,
  nameservers text NULL,
  notes longtext NULL,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY domain_name (domain_name(191)),
  KEY customer_id (customer_id),
  KEY status_expiry (status,expires_on)
) {$collate};",

			'invoices'          => "CREATE TABLE {$t}invoices (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  invoice_number varchar(40) NOT NULL,
  customer_id bigint(20) unsigned NOT NULL,
  order_id bigint(20) unsigned NOT NULL DEFAULT 0,
  type varchar(20) NOT NULL DEFAULT 'order',
  status varchar(20) NOT NULL DEFAULT 'draft',
  issue_date date NOT NULL,
  due_date date NOT NULL,
  subtotal bigint(20) NOT NULL DEFAULT 0,
  discount_total bigint(20) NOT NULL DEFAULT 0,
  tax_total bigint(20) NOT NULL DEFAULT 0,
  credit_applied bigint(20) NOT NULL DEFAULT 0,
  total bigint(20) NOT NULL DEFAULT 0,
  amount_paid bigint(20) NOT NULL DEFAULT 0,
  public_token varchar(64) DEFAULT NULL,
  notes text NULL,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  paid_at_gmt datetime DEFAULT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY invoice_number (invoice_number),
  UNIQUE KEY public_token (public_token),
  KEY customer_id (customer_id),
  KEY order_id (order_id),
  KEY status_due (status,due_date)
) {$collate};",

			'invoice_items'     => "CREATE TABLE {$t}invoice_items (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  invoice_id bigint(20) unsigned NOT NULL,
  item_type varchar(20) NOT NULL DEFAULT 'item',
  description varchar(500) NOT NULL DEFAULT '',
  related_type varchar(20) NOT NULL DEFAULT '',
  related_id bigint(20) unsigned NOT NULL DEFAULT 0,
  quantity int(11) NOT NULL DEFAULT 1,
  unit_price bigint(20) NOT NULL DEFAULT 0,
  tax_rate_bp int(11) NOT NULL DEFAULT 0,
  tax_amount bigint(20) NOT NULL DEFAULT 0,
  total bigint(20) NOT NULL DEFAULT 0,
  period_start date DEFAULT NULL,
  period_end date DEFAULT NULL,
  PRIMARY KEY  (id),
  KEY invoice_id (invoice_id),
  KEY related (related_type,related_id)
) {$collate};",

			'payments'          => "CREATE TABLE {$t}payments (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  invoice_id bigint(20) unsigned NOT NULL,
  customer_id bigint(20) unsigned NOT NULL,
  type varchar(20) NOT NULL DEFAULT 'payment',
  gateway varchar(40) NOT NULL DEFAULT '',
  status varchar(20) NOT NULL DEFAULT 'pending',
  amount bigint(20) NOT NULL DEFAULT 0,
  transaction_id varchar(120) NOT NULL DEFAULT '',
  sender_number varchar(40) NOT NULL DEFAULT '',
  proof_attachment_id bigint(20) unsigned NOT NULL DEFAULT 0,
  note text NULL,
  verified_by bigint(20) unsigned NOT NULL DEFAULT 0,
  verified_at_gmt datetime DEFAULT NULL,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY invoice_id (invoice_id),
  KEY customer_id (customer_id),
  KEY status (status),
  KEY transaction_id (transaction_id)
) {$collate};",

			'credits'           => "CREATE TABLE {$t}credits (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  customer_id bigint(20) unsigned NOT NULL,
  amount bigint(20) NOT NULL DEFAULT 0,
  balance_after bigint(20) NOT NULL DEFAULT 0,
  reason varchar(255) NOT NULL DEFAULT '',
  related_type varchar(20) NOT NULL DEFAULT '',
  related_id bigint(20) unsigned NOT NULL DEFAULT 0,
  created_by bigint(20) unsigned NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY customer_id (customer_id)
) {$collate};",

			'coupons'           => "CREATE TABLE {$t}coupons (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  code varchar(60) NOT NULL,
  type varchar(20) NOT NULL DEFAULT 'percent',
  value bigint(20) NOT NULL DEFAULT 0,
  starts_on date DEFAULT NULL,
  expires_on date DEFAULT NULL,
  usage_limit int(11) NOT NULL DEFAULT 0,
  per_customer_limit int(11) NOT NULL DEFAULT 0,
  used_count int(11) NOT NULL DEFAULT 0,
  applies_to longtext NULL,
  status varchar(20) NOT NULL DEFAULT 'active',
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY code (code)
) {$collate};",

			'tax_rates'         => "CREATE TABLE {$t}tax_rates (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  tax_class varchar(40) NOT NULL DEFAULT '',
  country char(2) NOT NULL DEFAULT '',
  rate_bp int(11) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'active',
  sort_order int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  KEY tax_class (tax_class)
) {$collate};",

			'notices'           => "CREATE TABLE {$t}notices (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  body longtext NOT NULL,
  notice_type varchar(20) NOT NULL DEFAULT 'info',
  audience varchar(20) NOT NULL DEFAULT 'all',
  audience_ids longtext NULL,
  starts_at_gmt datetime DEFAULT NULL,
  ends_at_gmt datetime DEFAULT NULL,
  send_email tinyint(1) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'draft',
  created_by bigint(20) unsigned NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY status (status)
) {$collate};",

			'notice_reads'      => "CREATE TABLE {$t}notice_reads (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  notice_id bigint(20) unsigned NOT NULL,
  customer_id bigint(20) unsigned NOT NULL,
  dismissed tinyint(1) NOT NULL DEFAULT 0,
  read_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY notice_customer (notice_id,customer_id),
  KEY customer_id (customer_id)
) {$collate};",

			'reminder_rules'    => "CREATE TABLE {$t}reminder_rules (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(190) NOT NULL,
  rule_type varchar(30) NOT NULL,
  days_offset int(11) NOT NULL DEFAULT 0,
  channel varchar(20) NOT NULL DEFAULT 'email',
  template_key varchar(60) NOT NULL DEFAULT '',
  send_admin_copy tinyint(1) NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'active',
  sort_order int(11) NOT NULL DEFAULT 0,
  created_at_gmt datetime NOT NULL,
  updated_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY rule_type_status (rule_type,status)
) {$collate};",

			'reminder_log'      => "CREATE TABLE {$t}reminder_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  rule_id bigint(20) unsigned NOT NULL,
  target_type varchar(20) NOT NULL,
  target_id bigint(20) unsigned NOT NULL,
  scheduled_for date NOT NULL,
  status varchar(20) NOT NULL DEFAULT 'queued',
  sent_at_gmt datetime DEFAULT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY reminder_once (rule_id,target_type,target_id,scheduled_for),
  KEY target (target_type,target_id)
) {$collate};",

			'email_queue'       => "CREATE TABLE {$t}email_queue (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  to_email varchar(190) NOT NULL,
  to_name varchar(190) NOT NULL DEFAULT '',
  subject varchar(500) NOT NULL DEFAULT '',
  body_html longtext NULL,
  body_text longtext NULL,
  template_key varchar(60) NOT NULL DEFAULT '',
  related_type varchar(20) NOT NULL DEFAULT '',
  related_id bigint(20) unsigned NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'pending',
  attempts int(11) NOT NULL DEFAULT 0,
  last_error text NULL,
  send_after_gmt datetime NOT NULL,
  sent_at_gmt datetime DEFAULT NULL,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY status_send_after (status,send_after_gmt)
) {$collate};",

			'email_log'         => "CREATE TABLE {$t}email_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  to_email varchar(190) NOT NULL,
  subject varchar(500) NOT NULL DEFAULT '',
  template_key varchar(60) NOT NULL DEFAULT '',
  related_type varchar(20) NOT NULL DEFAULT '',
  related_id bigint(20) unsigned NOT NULL DEFAULT 0,
  status varchar(20) NOT NULL DEFAULT 'sent',
  error text NULL,
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY related (related_type,related_id),
  KEY created_at_gmt (created_at_gmt)
) {$collate};",

			'activity_log'      => "CREATE TABLE {$t}activity_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
  customer_id bigint(20) unsigned NOT NULL DEFAULT 0,
  object_type varchar(20) NOT NULL DEFAULT '',
  object_id bigint(20) unsigned NOT NULL DEFAULT 0,
  action varchar(60) NOT NULL,
  message text NULL,
  context longtext NULL,
  ip_address varchar(45) NOT NULL DEFAULT '',
  created_at_gmt datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY customer_id (customer_id),
  KEY object (object_type,object_id),
  KEY created_at_gmt (created_at_gmt)
) {$collate};",
		);
	}
}