-- =====================================================================
-- Section 3: master data must be unique (per company, case-insensitive).
-- Run in phpMyAdmin against wbcorp_webapp.
--
-- STEP 1 - run these SELECTs first. Each should return NO rows. If any rows
-- come back, those names are already duplicated: rename or delete the extra
-- copies (or set them Inactive and rename) BEFORE step 2, otherwise the ALTER
-- fails with "Duplicate entry".
-- =====================================================================
SELECT 'customer' AS master, company_id, customer_name AS name, COUNT(*) AS copies FROM tblCustomer_Master GROUP BY company_id, customer_name HAVING COUNT(*) > 1;
SELECT 'supplier' AS master, company_id, supplier_name AS name, COUNT(*) AS copies FROM tblSupplier_Master GROUP BY company_id, supplier_name HAVING COUNT(*) > 1;
SELECT 'driver' AS master, company_id, driver_name AS name, COUNT(*) AS copies FROM tblDriver_Master GROUP BY company_id, driver_name HAVING COUNT(*) > 1;
SELECT 'transporter' AS master, company_id, transporter_name AS name, COUNT(*) AS copies FROM tblTransporter_Master GROUP BY company_id, transporter_name HAVING COUNT(*) > 1;
SELECT 'item' AS master, company_id, product_name AS name, COUNT(*) AS copies FROM tblProduct_Master GROUP BY company_id, product_name HAVING COUNT(*) > 1;

-- =====================================================================
-- STEP 2 - add the unique indexes (Vehicle and Transaction Type already have
-- one). "Duplicate key name" on a line means that index already exists - skip it.
-- =====================================================================
ALTER TABLE tblCustomer_Master    ADD UNIQUE KEY uq_customer_name    (company_id, customer_name);
ALTER TABLE tblSupplier_Master    ADD UNIQUE KEY uq_supplier_name    (company_id, supplier_name);
ALTER TABLE tblDriver_Master      ADD UNIQUE KEY uq_driver_name      (company_id, driver_name);
ALTER TABLE tblTransporter_Master ADD UNIQUE KEY uq_transporter_name (company_id, transporter_name);
ALTER TABLE tblProduct_Master     ADD UNIQUE KEY uq_product_name     (company_id, product_name);