-- Guarantors sign their own agreement (the guarantee and a privacy notice), separately from the customer.

ALTER TABLE policy_documents
  ADD COLUMN kind ENUM('customer', 'guarantor') NOT NULL DEFAULT 'customer' AFTER version;

CREATE TABLE guarantor_agreements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  guarantor_id INT UNSIGNED NOT NULL,
  -- Who signed: hash of the guarantor's name, phone and ID at signing. If those details are edited
  -- later (a different person), the signature no longer counts and the guarantor must sign again.
  guarantor_fingerprint CHAR(64) NOT NULL,
  policy_version VARCHAR(30) NOT NULL,
  -- SHA-256 of the exact text accepted
  policy_hash CHAR(64) NOT NULL,
  signed_name VARCHAR(160) NOT NULL,
  -- The most the guarantor agreed to pay (the loan amount at signing) and what it covered
  guaranteed_amount DECIMAL(14,2) NOT NULL,
  guarantee_summary VARCHAR(500) NOT NULL,
  accepted_guarantee TINYINT(1) NOT NULL,
  accepted_privacy TINYINT(1) NOT NULL,
  accepted_at DATETIME NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  presented_by_type ENUM('admin', 'agent') NOT NULL,
  presented_by_id INT UNSIGNED NOT NULL,
  presented_by_name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_guarantor_agreements_customer (customer_id, id),
  CONSTRAINT fk_guarantor_agreements_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_guarantor_agreements_guarantor FOREIGN KEY (guarantor_id) REFERENCES guarantors (id),
  CONSTRAINT fk_guarantor_agreements_policy FOREIGN KEY (policy_version) REFERENCES policy_documents (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;