-- Multiple fleet bicycles per maintenance job (same job_no / work order)
-- Idempotent: safe to re-run migrations

CREATE TABLE IF NOT EXISTS maintenance_job_bicycles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_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_maintenance_job_bicycle (job_id, bicycle_id),
  KEY idx_mjb_job (job_id),
  KEY idx_mjb_bicycle (bicycle_id),
  CONSTRAINT fk_mjb_job FOREIGN KEY (job_id) REFERENCES maintenance_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_mjb_bicycle FOREIGN KEY (bicycle_id) REFERENCES bicycles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO maintenance_job_bicycles (job_id, bicycle_id, line_no)
SELECT m.id, m.bicycle_id, 1
FROM maintenance_jobs m
WHERE m.bicycle_type = 'OUR'
  AND m.bicycle_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM maintenance_job_bicycles mjb WHERE mjb.job_id = m.id
  );