USE phone_display_admin;

-- If you are upgrading the OLD database:
ALTER TABLE products DROP COLUMN bulk_price;

ALTER TABLE compatible_models
ADD COLUMN brand_id INT UNSIGNED NULL AFTER product_id;

-- Existing compatibility rows need a brand. Set them temporarily to the
-- display's brand. You can edit them later to another brand such as Realme.
UPDATE compatible_models c
JOIN products p ON p.id=c.product_id
SET c.brand_id=p.brand_id
WHERE c.brand_id IS NULL;

ALTER TABLE compatible_models
MODIFY brand_id INT UNSIGNED NOT NULL;

ALTER TABLE compatible_models
ADD CONSTRAINT fk_compat_brand FOREIGN KEY (brand_id) REFERENCES brands(id)
ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE compatible_models
DROP INDEX uq_product_model,
ADD UNIQUE KEY uq_compat(product_id,brand_id,model_name);
