CREATE TABLE IF NOT EXISTS sites (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  address VARCHAR(500) NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  radius_meters INT NOT NULL DEFAULT 300,
  task_data JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX idx_sites_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(60) NOT NULL DEFAULT '',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX idx_users_active_name (active, full_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shifts (
  id CHAR(36) PRIMARY KEY,
  site_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  cleaner_name VARCHAR(180) NOT NULL,
  cleaner_contact VARCHAR(190) NOT NULL,
  access_code_hash CHAR(64) NOT NULL,
  area VARCHAR(250) NOT NULL,
  shift_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  status ENUM('Scheduled','In progress','Complete') NOT NULL DEFAULT 'Scheduled',
  task_data JSON NOT NULL,
  clock_in_at DATETIME(3) NULL,
  clock_out_at DATETIME(3) NULL,
  clock_in_latitude DECIMAL(10,7) NULL,
  clock_in_longitude DECIMAL(10,7) NULL,
  clock_out_latitude DECIMAL(10,7) NULL,
  clock_out_longitude DECIMAL(10,7) NULL,
  clock_in_distance DECIMAL(9,2) NULL,
  clock_out_distance DECIMAL(9,2) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_shifts_site FOREIGN KEY (site_id) REFERENCES sites(id),
  CONSTRAINT fk_shifts_user FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_shifts_date (shift_date),
  INDEX idx_shifts_site_date (site_id, shift_date),
  INDEX idx_shifts_cleaner_date (cleaner_contact, shift_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reports (
  id CHAR(36) PRIMARY KEY,
  shift_id CHAR(36) NOT NULL UNIQUE,
  cleaner VARCHAR(180) NOT NULL,
  site VARCHAR(180) NOT NULL,
  area VARCHAR(250) NOT NULL,
  completed INT NOT NULL,
  total INT NOT NULL,
  clock_in DATETIME(3) NOT NULL,
  clock_out DATETIME(3) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Complete',
  notes TEXT NOT NULL,
  task_data JSON NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_reports_shift FOREIGN KEY (shift_id) REFERENCES shifts(id),
  INDEX idx_reports_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS report_photos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  report_id CHAR(36) NOT NULL,
  object_key VARCHAR(255) NOT NULL UNIQUE,
  file_name VARCHAR(255) NOT NULL,
  content_type VARCHAR(100) NOT NULL,
  created_at DATETIME(3) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  CONSTRAINT fk_photos_report FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
  INDEX idx_report_photos_report_id (report_id),
  INDEX idx_report_photos_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;