-- Jalankan patch ini pada database automan_hypesearch

ALTER TABLE device
    ADD COLUMN device_type_id INT(11) NOT NULL AFTER device_name;

ALTER TABLE device
    ADD UNIQUE KEY uniq_device_code (device_id),
    ADD KEY idx_device_type_id (device_type_id);

ALTER TABLE coordinates
    DROP INDEX uniq_coordinate_name,
    ADD UNIQUE KEY uniq_device_type_coordinate (device_type_id, coordinate_name);

-- Optional FK jika engine dan data sudah siap
-- ALTER TABLE device
--     ADD CONSTRAINT fk_device_device_type FOREIGN KEY (device_type_id) REFERENCES device_type(id);
-- 
-- ALTER TABLE coordinates
--     ADD CONSTRAINT fk_coordinates_device_type FOREIGN KEY (device_type_id) REFERENCES device_type(id);