-- Arti Atolye ERP - Stone Core v2 Patch
-- Safe patch for missing fancy sizes, corrected carat refs, and auto stone generation.
SET NAMES utf8mb4;

-- 1) Ensure common currencies are represented in existing supplier/purchase columns by UI. No DB column needed.

-- 2) Fancy-group size/carat corrections from uploaded STONES.xlsx / carat sizes sheet.
-- Helper notes: size_code uses length*100 and width*100: 3x1.5 => 0300X0150.

-- BAGUETTE
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0200X0100', '2.00 × 1.00 mm', NULL, 2.00, 1.00 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.02000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0200X0100' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.06000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.14000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- MARQUISE
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0500X0250', '5.00 × 2.50 mm', NULL, 5.00, 2.50 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0600X0300', '6.00 × 3.00 mm', NULL, 6.00, 3.00 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.02500, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.08000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.12000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0250' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.20000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0300' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- PEAR
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0500X0250', '5.00 × 2.50 mm', NULL, 5.00, 2.50 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0600X0300', '6.00 × 3.00 mm', NULL, 6.00, 3.00 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.05000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.10000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.15000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0250' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.20000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0300' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- OVAL values from STONES.xlsx; update existing values if any are different.
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.15000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0300' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.25000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0300' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.50000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0400' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.75000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0700X0500' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 1.25000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0800X0600' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 2.00000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0900X0700' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- Update all existing stone cards to current carat reference where cut+size match.
UPDATE stones st
JOIN stone_carat_references ref ON ref.cut_id = st.cut_id AND ref.size_id = st.size_id AND ref.is_active=1
SET st.carat_each = ref.carat_each;

-- Create missing stone cards for all allowed combinations across all cut/size references.
-- Allowed combinations:
-- DIA / LD / MO => Colorless only
-- ZR => all configured colors
-- natural colored stones => same color as species code only
INSERT IGNORE INTO stones (stone_sku, species_id, cut_id, size_id, color_id, carat_each, critical_stock_qty)
SELECT
    CONCAT('ST-', sp.species_code, '-', cu.cut_code, '-', sz.size_code, '-', co.color_code) AS stone_sku,
    sp.id, cu.id, sz.id, co.id, ref.carat_each, 0
FROM stone_species sp
JOIN stone_cuts cu
JOIN stone_sizes sz ON sz.cut_id = cu.id AND sz.is_active=1
JOIN stone_carat_references ref ON ref.cut_id=cu.id AND ref.size_id=sz.id AND ref.is_active=1
JOIN stone_colors co ON co.is_active=1
WHERE sp.is_active=1
  AND (
      (sp.species_code IN ('DIA','LD','MO') AND co.color_code='CLR')
      OR (sp.species_code='ZR')
      OR (sp.species_code NOT IN ('DIA','LD','MO','ZR') AND sp.species_code = co.color_code)
  );

-- Safety: update inserted/old stone carat values one more time from reference.
UPDATE stones st
JOIN stone_carat_references ref ON ref.cut_id = st.cut_id AND ref.size_id = st.size_id AND ref.is_active=1
SET st.carat_each = ref.carat_each;
-- Arti Atolye ERP - Stone Core v2 Patch
-- Safe patch for missing fancy sizes, corrected carat refs, and auto stone generation.
SET NAMES utf8mb4;

-- 1) Ensure common currencies are represented in existing supplier/purchase columns by UI. No DB column needed.

-- 2) Fancy-group size/carat corrections from uploaded STONES.xlsx / carat sizes sheet.
-- Helper notes: size_code uses length*100 and width*100: 3x1.5 => 0300X0150.

-- BAGUETTE
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0200X0100', '2.00 × 1.00 mm', NULL, 2.00, 1.00 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='BAG'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.02000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0200X0100' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.06000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.14000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='BAG'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- MARQUISE
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0500X0250', '5.00 × 2.50 mm', NULL, 5.00, 2.50 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0600X0300', '6.00 × 3.00 mm', NULL, 6.00, 3.00 FROM stone_cuts WHERE cut_code='MRQ'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.02500, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.08000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.12000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0250' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.20000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0300' WHERE c.cut_code='MRQ'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- PEAR
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0300X0150', '3.00 × 1.50 mm', NULL, 3.00, 1.50 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0400X0200', '4.00 × 2.00 mm', NULL, 4.00, 2.00 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0500X0250', '5.00 × 2.50 mm', NULL, 5.00, 2.50 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;
INSERT INTO stone_sizes (cut_id, size_code, size_label, diameter_mm, length_mm, width_mm)
SELECT id, '0600X0300', '6.00 × 3.00 mm', NULL, 6.00, 3.00 FROM stone_cuts WHERE cut_code='PEAR'
ON DUPLICATE KEY UPDATE size_label=VALUES(size_label), length_mm=VALUES(length_mm), width_mm=VALUES(width_mm), is_active=1;

INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.05000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0300X0150' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.10000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0200' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.15000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0250' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.20000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0300' WHERE c.cut_code='PEAR'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- OVAL values from STONES.xlsx; update existing values if any are different.
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.15000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0400X0300' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.25000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0500X0300' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.50000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0600X0400' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 0.75000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0700X0500' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 1.25000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0800X0600' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;
INSERT INTO stone_carat_references (cut_id, size_id, carat_each, source_note)
SELECT c.id, s.id, 2.00000, 'STONES.xlsx carat sizes sheet v2' FROM stone_cuts c JOIN stone_sizes s ON s.cut_id=c.id AND s.size_code='0900X0700' WHERE c.cut_code='OVL'
ON DUPLICATE KEY UPDATE carat_each=VALUES(carat_each), source_note=VALUES(source_note), is_active=1;

-- Update all existing stone cards to current carat reference where cut+size match.
UPDATE stones st
JOIN stone_carat_references ref ON ref.cut_id = st.cut_id AND ref.size_id = st.size_id AND ref.is_active=1
SET st.carat_each = ref.carat_each;

-- Create missing stone cards for all allowed combinations across all cut/size references.
-- Allowed combinations:
-- DIA / LD / MO => Colorless only
-- ZR => all configured colors
-- natural colored stones => same color as species code only
INSERT IGNORE INTO stones (stone_sku, species_id, cut_id, size_id, color_id, carat_each, critical_stock_qty)
SELECT
    CONCAT('ST-', sp.species_code, '-', cu.cut_code, '-', sz.size_code, '-', co.color_code) AS stone_sku,
    sp.id, cu.id, sz.id, co.id, ref.carat_each, 0
FROM stone_species sp
JOIN stone_cuts cu
JOIN stone_sizes sz ON sz.cut_id = cu.id AND sz.is_active=1
JOIN stone_carat_references ref ON ref.cut_id=cu.id AND ref.size_id=sz.id AND ref.is_active=1
JOIN stone_colors co ON co.is_active=1
WHERE sp.is_active=1
  AND (
      (sp.species_code IN ('DIA','LD','MO') AND co.color_code='CLR')
      OR (sp.species_code='ZR')
      OR (sp.species_code NOT IN ('DIA','LD','MO','ZR') AND sp.species_code = co.color_code)
  );

-- Safety: update inserted/old stone carat values one more time from reference.
UPDATE stones st
JOIN stone_carat_references ref ON ref.cut_id = st.cut_id AND ref.size_id = st.size_id AND ref.is_active=1
SET st.carat_each = ref.carat_each;

-- ------------------------------------------------------------
-- Stone Core v3: correction/cancel infrastructure
-- ------------------------------------------------------------

-- cPanel/MySQL compatibility: ADD COLUMN IF NOT EXISTS is not supported on this server.
-- Use a small helper procedure to add columns only when missing.
DROP PROCEDURE IF EXISTS aa_add_column_if_missing;
DELIMITER $$
CREATE PROCEDURE aa_add_column_if_missing(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_definition TEXT)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @aa_sql = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN ', p_definition);
        PREPARE aa_stmt FROM @aa_sql;
        EXECUTE aa_stmt;
        DEALLOCATE PREPARE aa_stmt;
    END IF;
END$$
DELIMITER ;

CALL aa_add_column_if_missing('stone_purchase_lots', 'is_voided', '`is_voided` TINYINT(1) NOT NULL DEFAULT 0');
CALL aa_add_column_if_missing('stone_purchase_lots', 'voided_at', '`voided_at` DATETIME NULL');
CALL aa_add_column_if_missing('stone_purchase_lots', 'voided_by', '`voided_by` INT UNSIGNED NULL');
CALL aa_add_column_if_missing('stone_purchase_lots', 'void_reason', '`void_reason` TEXT NULL');
CALL aa_add_column_if_missing('stone_purchase_lots', 'corrected_from_lot_id', '`corrected_from_lot_id` BIGINT UNSIGNED NULL');
CALL aa_add_column_if_missing('stone_purchase_lots', 'updated_at', '`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP');

CALL aa_add_column_if_missing('stone_open_requests', 'deleted_at', '`deleted_at` DATETIME NULL');
CALL aa_add_column_if_missing('stone_open_requests', 'deleted_by', '`deleted_by` INT UNSIGNED NULL');

CREATE TABLE IF NOT EXISTS stone_admin_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id INT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    old_data LONGTEXT NULL,
    new_data LONGTEXT NULL,
    note TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_action (action_type),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recreate view so passive/voided records remain naturally corrected through stock movements.
CREATE OR REPLACE VIEW v_stone_stock_summary AS
SELECT
    st.id AS stone_id,
    st.stone_sku,
    sp.species_name,
    sc.cut_name,
    ss.size_label,
    co.color_name,
    st.carat_each,
    COALESCE(SUM(m.qty_change), 0) AS on_hand_qty,
    COALESCE(SUM(m.carat_change), 0) AS on_hand_carat,
    st.critical_stock_qty,
    CASE
        WHEN COALESCE(SUM(m.qty_change), 0) <= st.critical_stock_qty THEN 1
        ELSE 0
    END AS is_below_critical
FROM stones st
JOIN stone_species sp ON sp.id = st.species_id
JOIN stone_cuts sc ON sc.id = st.cut_id
JOIN stone_sizes ss ON ss.id = st.size_id
JOIN stone_colors co ON co.id = st.color_id
LEFT JOIN stone_stock_movements m ON m.stone_id = st.id
WHERE st.is_active = 1
GROUP BY st.id, st.stone_sku, sp.species_name, sc.cut_name, ss.size_label, co.color_name, st.carat_each, st.critical_stock_qty;

-- ------------------------------------------------------------
-- Stone Core v4: merge/transfer correction infrastructure
-- Cumulative patch from v1: safe to import once after v1.
-- ------------------------------------------------------------

CALL aa_add_column_if_missing('stones', 'merged_into_stone_id', '`merged_into_stone_id` INT UNSIGNED NULL');
CALL aa_add_column_if_missing('stones', 'archived_at', '`archived_at` DATETIME NULL');
CALL aa_add_column_if_missing('stones', 'archived_by', '`archived_by` INT UNSIGNED NULL');
CALL aa_add_column_if_missing('stones', 'archived_reason', '`archived_reason` TEXT NULL');

-- Keep requests easier to clean/trace. Already in v3 for deleted fields; harmless if present.
CALL aa_add_column_if_missing('stone_open_requests', 'deleted_at', '`deleted_at` DATETIME NULL');
CALL aa_add_column_if_missing('stone_open_requests', 'deleted_by', '`deleted_by` INT UNSIGNED NULL');

CREATE TABLE IF NOT EXISTS stone_admin_audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id INT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(80) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    old_data LONGTEXT NULL,
    new_data LONGTEXT NULL,
    note TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_action (action_type),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recreate stock summary view to hide archived/passive stone cards from normal stock list.
CREATE OR REPLACE VIEW v_stone_stock_summary AS
SELECT
    st.id AS stone_id,
    st.stone_sku,
    sp.species_name,
    sc.cut_name,
    ss.size_label,
    co.color_name,
    st.carat_each,
    COALESCE(SUM(m.qty_change), 0) AS on_hand_qty,
    COALESCE(SUM(m.carat_change), 0) AS on_hand_carat,
    st.critical_stock_qty,
    CASE
        WHEN COALESCE(SUM(m.qty_change), 0) <= st.critical_stock_qty THEN 1
        ELSE 0
    END AS is_below_critical
FROM stones st
JOIN stone_species sp ON sp.id = st.species_id
JOIN stone_cuts sc ON sc.id = st.cut_id
JOIN stone_sizes ss ON ss.id = st.size_id
JOIN stone_colors co ON co.id = st.color_id
LEFT JOIN stone_stock_movements m ON m.stone_id = st.id
WHERE st.is_active = 1
GROUP BY st.id, st.stone_sku, sp.species_name, sc.cut_name, ss.size_label, co.color_name, st.carat_each, st.critical_stock_qty;

-- Cleanup helper procedure used only during import.
DROP PROCEDURE IF EXISTS aa_add_column_if_missing;
