CREATE TABLE IF NOT EXISTS rekognition_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rekognition_events_event_name (event_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_images (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  s3_bucket VARCHAR(255) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  thumbnail_s3_key VARCHAR(1024) NULL,
  thumbnail_status ENUM('pending', 'ready', 'failed') NOT NULL DEFAULT 'pending',
  thumbnail_error TEXT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  processing_status ENUM('pending', 'indexed', 'no_face', 'failed') NOT NULL DEFAULT 'pending',
  detected_face_count INT UNSIGNED NULL,
  processing_error TEXT NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_images_s3_key (s3_key(255)),
  KEY idx_rekognition_images_thumbnail_status (thumbnail_status),
  UNIQUE KEY uq_images_event_original_name (event_id, original_name),
  KEY idx_rekognition_images_event_id (event_id),
  CONSTRAINT fk_rekognition_images_event_id
    FOREIGN KEY (event_id) REFERENCES rekognition_events(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_faces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  image_id BIGINT UNSIGNED NOT NULL,
  face_id VARCHAR(255) NOT NULL,
  external_image_id VARCHAR(255) NOT NULL,
  confidence DECIMAL(7,4) NULL,
  bounding_box JSON NULL,
  indexed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_faces_face_id (face_id),
  KEY idx_faces_image_id (image_id),
  CONSTRAINT fk_faces_image_id
    FOREIGN KEY (image_id) REFERENCES rekognition_images(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_atmosphere_images (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  s3_bucket VARCHAR(255) NOT NULL,
  s3_key VARCHAR(1024) NOT NULL,
  thumbnail_s3_key VARCHAR(1024) NULL,
  thumbnail_status ENUM('pending', 'ready', 'failed') NOT NULL DEFAULT 'pending',
  thumbnail_error TEXT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  uploaded_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_atmosphere_images_s3_key (s3_key(255)),
  KEY idx_atmosphere_images_thumbnail_status (thumbnail_status),
  UNIQUE KEY uq_atmosphere_images_event_original_name (event_id, original_name),
  KEY idx_atmosphere_images_event_id (event_id),
  CONSTRAINT fk_atmosphere_images_event_id
    FOREIGN KEY (event_id) REFERENCES rekognition_events(id)
    ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_otp_verifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NOT NULL,
  otp_code VARCHAR(12) NOT NULL,
  otp_ref VARCHAR(16) NOT NULL,
  otp_hash VARCHAR(255) NOT NULL,
  status ENUM('sent', 'verified', 'expired', 'failed') NOT NULL DEFAULT 'sent',
  request_ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  verified_at TIMESTAMP NULL,
  expires_at TIMESTAMP NOT NULL,
  KEY idx_rekognition_otp_phone_status (phone, status),
  KEY idx_rekognition_otp_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_access_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NULL,
  event_type VARCHAR(64) NOT NULL,
  otp_verification_id BIGINT UNSIGNED NULL,
  request_ip VARCHAR(45) NULL,
  user_agent VARCHAR(512) NULL,
  details JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rekognition_access_logs_phone (phone),
  KEY idx_rekognition_access_logs_event_type (event_type),
  KEY idx_rekognition_access_logs_created_at (created_at),
  CONSTRAINT fk_rekognition_access_logs_otp
    FOREIGN KEY (otp_verification_id) REFERENCES rekognition_otp_verifications(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rekognition_customer_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_group VARCHAR(255) 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 VARCHAR(255) 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,
  assistant_name VARCHAR(255) NULL,
  assistant_email VARCHAR(255) NULL,
  assistant_phone VARCHAR(32) 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_key_insights (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id BIGINT UNSIGNED NOT NULL,
  insight_key VARCHAR(255) NOT NULL,
  insight_value TEXT NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_profile_key_insights_customer (customer_id, sort_order),
  CONSTRAINT fk_profile_key_insights_customer
    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_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_business_cards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  side ENUM('front', 'back') 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,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_customer_profile_business_card_side (customer_id, side),
  CONSTRAINT fk_customer_profile_business_cards_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;
