-- Customer records: signed agreements, uploaded documents, sale details and guarantors.

-- Every published version of the Terms & Privacy Policy, exactly as shown to customers.
-- Rows are written on startup from src/policies and never changed afterwards.
CREATE TABLE policy_documents (
  version VARCHAR(30) PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  content MEDIUMTEXT NOT NULL,
  content_hash CHAR(64) NOT NULL,
  published_at DATE NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A customer's acceptance of a policy version, signed by typing their full legal name.
CREATE TABLE customer_agreements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  policy_version VARCHAR(30) NOT NULL,
  -- SHA-256 of the exact text accepted, to prove later what the customer saw
  policy_hash CHAR(64) NOT NULL,
  signed_name VARCHAR(160) NOT NULL,
  accepted_terms 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,
  -- The agent (or admin) who presented the terms to the customer
  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_agreements_customer (customer_id, id),
  CONSTRAINT fk_agreements_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_agreements_policy FOREIGN KEY (policy_version) REFERENCES policy_documents (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One guarantor per customer (when the sale needs one).
CREATE TABLE guarantors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(160) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(190) NULL,
  address VARCHAR(255) NOT NULL,
  relationship VARCHAR(60) NOT NULL,
  occupation VARCHAR(120) NULL,
  id_type VARCHAR(30) NOT NULL,
  -- ID number encrypted at rest (AES-256-GCM); last 4 digits kept for display
  id_number_encrypted VARCHAR(255) NOT NULL,
  id_number_last4 VARCHAR(4) NOT NULL,
  created_by_type ENUM('admin', 'agent') NOT NULL,
  created_by_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_guarantors_customer (customer_id),
  CONSTRAINT fk_guarantors_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Uploaded files (photos, ID documents, supporting documents). Uploaded first as drafts
-- (customer_id NULL) while the agent fills in the form, then linked when onboarding is saved.
CREATE TABLE stored_files (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id CHAR(32) NOT NULL,
  customer_id INT UNSIGNED NULL,
  kind ENUM('customer_photo', 'customer_id_document', 'customer_document', 'device_photo',
            'guarantor_photo', 'guarantor_id_document', 'guarantor_document') NOT NULL,
  label VARCHAR(120) NULL,
  original_name VARCHAR(200) NOT NULL,
  mime_type VARCHAR(60) NOT NULL,
  size_bytes INT UNSIGNED NOT NULL,
  sha256 CHAR(64) NOT NULL,
  storage ENUM('database', 'local') NOT NULL,
  storage_key VARCHAR(255) NULL,
  uploaded_by_type ENUM('admin', 'agent') NOT NULL,
  uploaded_by_id INT UNSIGNED NOT NULL,
  -- Files are never hard-deleted once linked to a customer; an admin can archive a wrong upload.
  archived_at DATETIME NULL,
  archived_by INT UNSIGNED NULL,
  archive_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_files_public_id (public_id),
  KEY ix_files_customer (customer_id, kind),
  KEY ix_files_drafts (uploaded_by_type, uploaded_by_id, customer_id, created_at),
  CONSTRAINT fk_files_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Encrypted file contents when FILE_STORAGE=database (the default), kept apart from the metadata.
CREATE TABLE stored_file_blobs (
  file_id INT UNSIGNED PRIMARY KEY,
  content LONGBLOB NOT NULL,
  CONSTRAINT fk_file_blobs_file FOREIGN KEY (file_id) REFERENCES stored_files (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sale details on the customer (one device per customer).
ALTER TABLE customers
  ADD COLUMN sale_reference VARCHAR(30) NULL AFTER start_date,
  ADD COLUMN sale_receipt_number VARCHAR(60) NULL AFTER sale_reference,
  ADD COLUMN sale_notes VARCHAR(500) NULL AFTER sale_receipt_number,
  ADD COLUMN guarantor_required TINYINT(1) NOT NULL DEFAULT 0 AFTER sale_notes,
  ADD UNIQUE KEY uq_customers_sale_reference (sale_reference);

-- Existing sales get a reference too: NXS-<year>-<customer number>
UPDATE customers
  SET sale_reference = CONCAT('NXS-', YEAR(created_at), '-', SUBSTRING(code, 5))
  WHERE sale_reference IS NULL AND code IS NOT NULL;