CREATE TABLE IF NOT EXISTS service_zips (
  id INT AUTO_INCREMENT PRIMARY KEY,
  postal_code VARCHAR(10) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS time_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date DATE NOT NULL,
  start_at TIME NOT NULL,
  end_at TIME NOT NULL,
  postal_code VARCHAR(10) NOT NULL,
  capacity SMALLINT UNSIGNED NOT NULL,
  booked_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_slot (date, start_at, end_at, postal_code),
  INDEX idx_zip_date (postal_code, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS deliveries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  postal_code VARCHAR(10) NOT NULL,
  time_slot_id INT NOT NULL,
  status ENUM('pending','confirmed','failed_push') NOT NULL DEFAULT 'confirmed',
  external_ref VARCHAR(64) NULL,
  payload JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_deliveries_slot FOREIGN KEY (time_slot_id) REFERENCES time_slots(id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;