-- Fixes a GST under-charging bug: menu_items.tax_id could only ever point at
-- ONE tax row, so an item taxed under CGST 6% never also charged its SGST 6%
-- counterpart — every GST invoice was collecting only half the tax actually
-- due. This adds a proper many-to-many link (an item can carry both its CGST
-- and SGST rows at once, or a single IGST row for inter-state) and backfills
-- it from the existing data, auto-pairing each item's current CGST/SGST tax
-- with its same-rate counterpart in the same company where one exists.
--
-- Only needed if your `menu_items` table predates this change — a fresh
-- `mysql -u root -p < schema.sql` import already includes menu_item_taxes.
--
-- Usage: mysql -u root -p petzy_pos < add_menu_item_taxes.sql

CREATE TABLE IF NOT EXISTS menu_item_taxes (
    item_id INT UNSIGNED NOT NULL,
    tax_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (item_id, tax_id),
    CONSTRAINT fk_mit_item FOREIGN KEY (item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_mit_tax FOREIGN KEY (tax_id) REFERENCES taxes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 1) Preserve whatever each item was already charging.
INSERT IGNORE INTO menu_item_taxes (item_id, tax_id)
SELECT mi.id, mi.tax_id FROM menu_items mi WHERE mi.tax_id IS NOT NULL;

-- 2) Auto-pair CGST <-> SGST: if an item's existing tax is a CGST or SGST row,
-- also attach the matching-rate counterpart (same company, opposite type) so
-- the two components that make up one real-world GST slab travel together.
INSERT IGNORE INTO menu_item_taxes (item_id, tax_id)
SELECT mi.id, counterpart.id
FROM menu_items mi
JOIN taxes t ON t.id = mi.tax_id AND t.type = 'CGST'
JOIN taxes counterpart ON counterpart.company_id = t.company_id
    AND counterpart.type = 'SGST' AND counterpart.rate = t.rate;

INSERT IGNORE INTO menu_item_taxes (item_id, tax_id)
SELECT mi.id, counterpart.id
FROM menu_items mi
JOIN taxes t ON t.id = mi.tax_id AND t.type = 'SGST'
JOIN taxes counterpart ON counterpart.company_id = t.company_id
    AND counterpart.type = 'CGST' AND counterpart.rate = t.rate;

-- Note: menu_items.tax_id is left in place (now legacy/unused by pricing) so
-- nothing else that reads it breaks; the app reads menu_item_taxes going forward.