CREATE TABLE IF NOT EXISTS rekognition_customer_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_group ENUM('TP', 'TAAP', 'TNS', 'TBS', 'TMS', 'TDS', 'TESM', 'TCVA', 'TB GROUP') NULL,
  first_name_th VARCHAR(120) NULL,
  first_name_en VARCHAR(120) NULL,
  last_name_th VARCHAR(120) NULL,
  last_name_en VARCHAR(120) NULL,
  nickname_th VARCHAR(120) NULL,
  nickname_en VARCHAR(120) NULL,
  company_th VARCHAR(255) NULL,
  company_en VARCHAR(255) NULL,
  nickname VARCHAR(120) NULL,
  position VARCHAR(255) NULL,
  phone VARCHAR(32) NULL,
  email VARCHAR(255) NULL,
  line_id VARCHAR(120) NULL,
  business_type VARCHAR(255) NULL,
  importance_level ENUM('1', '2', '3', 'VIP', 'VVIP', 'Corporate', 'Prospect') NULL,
  birthday DATE NULL,
  pic ENUM('PV', 'RV', 'SA', 'SS', 'UT') NULL,
  contact_person VARCHAR(255) NULL,
  customer_status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  relationship_with_company VARCHAR(255) NULL,
  current_car VARCHAR(255) NULL,
  car_registration_number VARCHAR(100) NULL,
  interested_model VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_customer_profiles_company_group (company_group),
  KEY idx_customer_profiles_name_th (first_name_th, last_name_th),
  KEY idx_customer_profiles_company_th (company_th),
  KEY idx_customer_profiles_phone (phone),
  KEY idx_customer_profiles_email (email),
  KEY idx_customer_profiles_importance (importance_level),
  KEY idx_customer_profiles_pic (pic),
  KEY idx_customer_profiles_status (customer_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_customer_profile_faces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  face_id VARCHAR(255) NOT NULL,
  collection_id VARCHAR(255) NOT NULL,
  s3_bucket VARCHAR(255) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  confidence DECIMAL(7,4) NULL,
  bounding_box JSON NULL,
  indexed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_profile_faces_face_id (face_id),
  KEY idx_customer_profile_faces_customer_id (customer_id),
  CONSTRAINT fk_customer_profile_faces_customer_id
    FOREIGN KEY (customer_id) REFERENCES rekognition_customer_profiles(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_customer_profile_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  event_year SMALLINT UNSIGNED NULL,
  event_name VARCHAR(255) NOT NULL,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_customer_profile_events_customer_id (customer_id),
  KEY idx_customer_profile_events_year (event_year),
  KEY idx_customer_profile_events_name (event_name),
  CONSTRAINT fk_customer_profile_events_customer_id
    FOREIGN KEY (customer_id) REFERENCES rekognition_customer_profiles(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;