22 lines
976 B
SQL
22 lines
976 B
SQL
-- Migration to make items, services, categories and customers shared between branches.
|
|
-- Only sales (orders) remain branch-specific.
|
|
|
|
-- Step 0: Ensure branch_id is indexed separately so we can drop the unique index later.
|
|
ALTER TABLE prices ADD INDEX idx_branch_id (branch_id);
|
|
|
|
-- Step 1: Cleanup duplicate prices across different branches for the same item/service.
|
|
-- We keep the oldest record (lowest ID).
|
|
DELETE p1 FROM prices p1
|
|
INNER JOIN prices p2
|
|
ON p1.item_id = p2.item_id
|
|
AND p1.service_id = p2.service_id
|
|
AND p1.id > p2.id;
|
|
|
|
-- Step 2: Update unique index for prices to be global (item_id + service_id only).
|
|
-- Note: Dropping the index might require idx_branch_id to exist for the foreign key.
|
|
DROP INDEX unique_price ON prices;
|
|
CREATE UNIQUE INDEX unique_price ON prices (item_id, service_id);
|
|
|
|
-- Step 3: Make branch_id optional in prices and customers tables.
|
|
ALTER TABLE prices MODIFY branch_id INT NULL;
|
|
ALTER TABLE customers MODIFY branch_id INT NULL; |