-- Multiple customer-owned bicycles per maintenance job (same job_no)
-- Idempotent: safe to re-run migrations

CREATE TABLE IF NOT EXISTS maintenance_job_customer_bikes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  line_no INT NOT NULL DEFAULT 1,
  brand VARCHAR(100) NOT NULL,
  model VARCHAR(100) NULL DEFAULT NULL,
  color VARCHAR(50) NULL DEFAULT NULL,
  serial VARCHAR(100) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mjcb_job (job_id),
  CONSTRAINT fk_mjcb_job FOREIGN KEY (job_id) REFERENCES maintenance_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO maintenance_job_customer_bikes (job_id, line_no, brand, model, color, serial)
SELECT m.id, 1, m.customer_bike_brand, m.customer_bike_model, m.customer_bike_color, m.customer_bike_serial
FROM maintenance_jobs m
WHERE m.bicycle_type = 'CUSTOMER'
  AND m.customer_bike_brand IS NOT NULL
  AND TRIM(m.customer_bike_brand) <> ''
  AND NOT EXISTS (
    SELECT 1 FROM maintenance_job_customer_bikes mjcb WHERE mjcb.job_id = m.id
  );