-- Arti Atolye ERP - CSV to Production List v1
-- Safe migration. Does not drop data.
-- Purpose: make sure CSV-approved order rows have the columns used by production.php/order_detail.php.

SET NAMES utf8mb4;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='shipped_date')=0,
    'ALTER TABLE order_items ADD COLUMN shipped_date DATE NULL AFTER ship_by_date',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='store_note')=0,
    'ALTER TABLE order_items ADD COLUMN store_note TEXT NULL AFTER raw_csv_json',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='admin_note')=0,
    'ALTER TABLE order_items ADD COLUMN admin_note TEXT NULL AFTER store_note',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND INDEX_NAME='idx_order_items_status_ship')=0,
    'ALTER TABLE order_items ADD INDEX idx_order_items_status_ship (current_status_id, ship_by_date, sale_date)',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND INDEX_NAME='idx_order_items_store_status')=0,
    'ALTER TABLE order_items ADD INDEX idx_order_items_store_status (store_id, current_status_id)',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Make sure core statuses exist.
INSERT INTO production_statuses (status_code, status_name, sort_order, is_final, is_cancelled, is_active) VALUES
('new_order', 'Yeni Sipariş', 10, 0, 0, 1),
('stl_ready', 'STL Hazırlandı', 20, 0, 0, 1),
('casting', 'Döküm', 30, 0, 0, 1),
('workshop', 'Atölye', 40, 0, 0, 1),
('polish_arrival', 'Cila Geliş', 50, 0, 0, 1),
('setting_entry', 'Mıhlama Giriş', 60, 0, 0, 1),
('setting_arrival', 'Mıhlama Geliş', 70, 0, 0, 1),
('tuy_entry', 'Tüy Giriş', 80, 0, 0, 1),
('tuy_arrival', 'Tüy Geliş', 90, 0, 0, 1),
('quality_control', 'Kalite Kontrol', 100, 0, 0, 1),
('packaging', 'Paketleme', 110, 0, 0, 1),
('shipping', 'Kargo', 120, 0, 0, 1),
('completed', 'Tamamlandı', 130, 1, 0, 1),
('cancelled', 'İptal', 140, 1, 1, 1)
ON DUPLICATE KEY UPDATE status_name=VALUES(status_name), sort_order=VALUES(sort_order), is_final=VALUES(is_final), is_cancelled=VALUES(is_cancelled), is_active=1;

-- v1.1 quantity split / duplicate protection columns
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='source_transaction_id')=0,
    'ALTER TABLE order_items ADD COLUMN source_transaction_id VARCHAR(64) NULL AFTER transaction_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='quantity_unit_index')=0,
    'ALTER TABLE order_items ADD COLUMN quantity_unit_index INT UNSIGNED NOT NULL DEFAULT 1 AFTER quantity',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND COLUMN_NAME='quantity_unit_total')=0,
    'ALTER TABLE order_items ADD COLUMN quantity_unit_total INT UNSIGNED NOT NULL DEFAULT 1 AFTER quantity_unit_index',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='order_items' AND INDEX_NAME='idx_order_items_source_transaction')=0,
    'ALTER TABLE order_items ADD INDEX idx_order_items_source_transaction (source_transaction_id)',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
