-- 1.0.10: quotes, client groups, downloads, GDPR requests, SMS, sign-in with Google, dunning, more gateways

-- Quotes (proposals): accepted by the client in the client area, then turned into an invoice
CREATE TABLE quotes (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  number VARCHAR(32) NOT NULL DEFAULT '',
  subject VARCHAR(250) NOT NULL DEFAULT '',
  status VARCHAR(12) NOT NULL DEFAULT 'Draft' COMMENT 'Draft, Delivered, Accepted, Declined, Expired',
  valid_until DATE NULL,
  currency_id INT UNSIGNED NOT NULL DEFAULT 1,
  subtotal 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,
  notes TEXT NULL COMMENT 'shown to the client',
  admin_notes TEXT NULL,
  invoice_id INT UNSIGNED NULL,
  accepted_at DATETIME NULL,
  accepted_by VARCHAR(190) NOT NULL DEFAULT '' COMMENT 'name typed by the client when accepting',
  accepted_ip VARCHAR(64) NOT NULL DEFAULT '',
  delivered_at DATETIME NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY client (client_id, status),
  KEY status (status, valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE quote_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  quote_id INT UNSIGNED NOT NULL,
  description VARCHAR(1000) NOT NULL,
  qty DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit_price DECIMAL(16,2) NOT NULL DEFAULT 0,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0,
  taxed TINYINT(1) NOT NULL DEFAULT 1,
  sort INT NOT NULL DEFAULT 0,
  KEY quote (quote_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Client groups: a colour in the admin area and an optional discount on every new invoice
CREATE TABLE client_groups (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(7) NOT NULL DEFAULT '',
  discount_pct DECIMAL(5,2) NOT NULL DEFAULT 0,
  no_late_fees TINYINT(1) NOT NULL DEFAULT 0,
  notes VARCHAR(500) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE clients ADD group_id INT UNSIGNED NULL AFTER status, ADD KEY group_id (group_id),
  ADD sms_opt_in TINYINT(1) NOT NULL DEFAULT 0,
  ADD anonymised_at DATETIME NULL;

-- Downloads in the client area (public, logged in, or only for clients with an active product)
CREATE TABLE download_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  sort INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE downloads (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  title VARCHAR(250) NOT NULL,
  description TEXT NULL,
  file VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'name under storage/downloads',
  filename VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'name the visitor gets',
  size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  access VARCHAR(10) NOT NULL DEFAULT 'clients' COMMENT 'public, clients, product',
  product_ids VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'comma separated, for access = product',
  published TINYINT(1) NOT NULL DEFAULT 1,
  downloads INT UNSIGNED NOT NULL DEFAULT 0,
  sort INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  KEY category (category_id, sort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- GDPR: data export and account deletion requests from the client area
CREATE TABLE gdpr_requests (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NULL,
  type VARCHAR(10) NOT NULL COMMENT 'export, delete',
  status VARCHAR(10) NOT NULL DEFAULT 'pending' COMMENT 'pending, done, declined',
  note VARCHAR(1000) NOT NULL DEFAULT '',
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  done_at DATETIME NULL,
  KEY status (status, type),
  KEY client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMS log (texts are short notices; never secrets)
CREATE TABLE sms_log (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  client_id INT UNSIGNED NULL,
  phone VARCHAR(32) NOT NULL,
  event VARCHAR(32) NOT NULL DEFAULT '',
  message VARCHAR(640) NOT NULL,
  ok TINYINT(1) NOT NULL DEFAULT 0,
  error VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  KEY client (client_id),
  KEY created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sign in with Google: the Google account id linked to a login
ALTER TABLE users ADD google_sub VARCHAR(64) NULL AFTER totp_last, ADD UNIQUE KEY google_sub (google_sub);

-- Dunning: when the next automatic card retry is due
ALTER TABLE invoices ADD dunning_next DATE NULL AFTER autocharge_at;

INSERT IGNORE INTO gateways (code, name, enabled, sort) VALUES
 ('mollie', 'Mollie', 0, 5),
 ('authorizenet', 'Credit card (Authorize.net)', 0, 6),
 ('btcpay', 'Cryptocurrency (BTCPay Server)', 0, 7);