-- Multiple bicycles per rental contract (same rental_no / signature)
-- Idempotent: safe to re-run migrations

CREATE TABLE IF NOT EXISTS rental_bicycles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rental_id BIGINT UNSIGNED NOT NULL,
  bicycle_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_rental_bicycle (rental_id, bicycle_id),
  KEY idx_rental_bicycles_rental (rental_id),
  KEY idx_rental_bicycles_bike (bicycle_id),
  CONSTRAINT fk_rental_bicycles_rental FOREIGN KEY (rental_id) REFERENCES rentals(id) ON DELETE CASCADE,
  CONSTRAINT fk_rental_bicycles_bicycle FOREIGN KEY (bicycle_id) REFERENCES bicycles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backfill existing single-bike rentals
INSERT INTO rental_bicycles (rental_id, bicycle_id, line_no)
SELECT r.id, r.bicycle_id, 1
FROM rentals r
WHERE r.bicycle_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM rental_bicycles rb WHERE rb.rental_id = r.id
  );