-- Run this on DB slmbredirects_guaranti
CREATE TABLE IF NOT EXISTS guarantee_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(20) NOT NULL UNIQUE,
  seller VARCHAR(100) NOT NULL,
  customer_name VARCHAR(120) NOT NULL,
  province VARCHAR(100) NOT NULL,
  address TEXT NOT NULL,
  mobile VARCHAR(32) NOT NULL,
  phone VARCHAR(32) DEFAULT NULL,
  model VARCHAR(120) NOT NULL,
  warranty VARCHAR(120) DEFAULT NULL,
  purchase_date VARCHAR(32) DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_seller (seller),
  KEY idx_province (province),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS installations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tracking_code VARCHAR(20) NOT NULL,
  installer_name VARCHAR(120) NOT NULL,
  install_date VARCHAR(32) NOT NULL,
  serial_number VARCHAR(120) NOT NULL,
  notes TEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_tracking (tracking_code),
  KEY idx_created (created_at),
  CONSTRAINT fk_install_req FOREIGN KEY (tracking_code) REFERENCES guarantee_requests(tracking_code) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
