-- Motorboy BusOps V1.5.2
-- Branches are linked to a canonical departure location.

SET @col_exists := (
 SELECT COUNT(*) FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='branches' AND COLUMN_NAME='location_id'
);
SET @sql := IF(@col_exists=0,'ALTER TABLE branches ADD COLUMN location_id BIGINT UNSIGNED NULL AFTER email','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
 SELECT COUNT(*) FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='branches' AND INDEX_NAME='idx_branch_location'
);
SET @sql := IF(@idx_exists=0,'ALTER TABLE branches ADD KEY idx_branch_location (location_id)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Best-effort mapping for existing branches using city/name.
UPDATE branches b
SET b.location_id=(
 SELECT l.id FROM locations l
 WHERE (l.operator_id=b.operator_id OR l.operator_id IS NULL)
   AND (LOWER(TRIM(l.city))=LOWER(TRIM(b.city)) OR LOWER(TRIM(l.name))=LOWER(TRIM(b.city)))
 ORDER BY (l.operator_id=b.operator_id) DESC,l.id ASC LIMIT 1
)
WHERE b.location_id IS NULL AND b.city IS NOT NULL AND TRIM(b.city)<>'';

SET @fk_exists := (
 SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_SCHEMA=DATABASE() AND TABLE_NAME='branches'
 AND CONSTRAINT_NAME='fk_branch_location' AND CONSTRAINT_TYPE='FOREIGN KEY'
);
SET @sql := IF(@fk_exists=0,'ALTER TABLE branches ADD CONSTRAINT fk_branch_location FOREIGN KEY (location_id) REFERENCES locations(id)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO system_settings(setting_key,setting_value)
VALUES('installed_version','1.5.2')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);