-- Arti Atolye ERP - CSV to Production v1.2 HOTFIX
-- Fixes orders.php / production pages 500 errors caused by partial installs or missing columns/tables.
-- Safe migration. Does not delete data.

SET NAMES utf8mb4;

-- Product image table used by product card and production list screens.
CREATE TABLE IF NOT EXISTS product_images (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NULL,
    image_alt VARCHAR(255) NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    uploaded_by INT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_images_product (product_id),
    INDEX idx_product_images_primary (product_id, is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='product_images' AND COLUMN_NAME='file_path')=0,
    'ALTER TABLE product_images ADD COLUMN file_path VARCHAR(255) NOT NULL DEFAULT '''' ', '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='product_images' AND COLUMN_NAME='is_primary')=0,
    'ALTER TABLE product_images ADD COLUMN is_primary TINYINT(1) NOT NULL DEFAULT 0', '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='product_images' AND COLUMN_NAME='sort_order')=0,
    'ALTER TABLE product_images ADD COLUMN sort_order INT UNSIGNED NOT NULL DEFAULT 0', '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='product_images' AND COLUMN_NAME='original_filename')=0,
    'ALTER TABLE product_images ADD COLUMN original_filename VARCHAR(255) NULL', '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='product_images' AND COLUMN_NAME='image_alt')=0,
    'ALTER TABLE product_images ADD COLUMN image_alt VARCHAR(255) NULL', '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='product_images' AND COLUMN_NAME='uploaded_by')=0,
    'ALTER TABLE product_images ADD COLUMN uploaded_by INT UNSIGNED NULL', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ensure order_items has all columns used by CSV approval, all-orders, production list and order detail screens.
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', '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='csv_import_log_id')=0,
    'ALTER TABLE order_items ADD COLUMN csv_import_log_id INT UNSIGNED NULL', '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='current_status_id')=0,
    'ALTER TABLE order_items ADD COLUMN current_status_id INT UNSIGNED NULL', '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='product_id')=0,
    'ALTER TABLE order_items ADD COLUMN product_id INT UNSIGNED NULL', '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='product_store_sku_id')=0,
    'ALTER TABLE order_items ADD COLUMN product_store_sku_id INT UNSIGNED NULL', '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='product_card_missing')=0,
    'ALTER TABLE order_items ADD COLUMN product_card_missing TINYINT(1) NOT NULL DEFAULT 0', '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='product_image_path')=0,
    'ALTER TABLE order_items ADD COLUMN product_image_path VARCHAR(255) NULL', '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='is_custom')=0,
    'ALTER TABLE order_items ADD COLUMN is_custom TINYINT(1) NOT NULL DEFAULT 0', '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', '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', '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='buyer_username')=0,
    'ALTER TABLE order_items ADD COLUMN buyer_username VARCHAR(190) NULL', '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='shipped_date')=0,
    'ALTER TABLE order_items ADD COLUMN shipped_date DATE NULL', '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='parsed_chain_length')=0,
    'ALTER TABLE order_items ADD COLUMN parsed_chain_length VARCHAR(60) NULL', '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='carrier')=0,
    'ALTER TABLE order_items ADD COLUMN carrier VARCHAR(80) NULL', '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='flagged_customer_match_text')=0,
    'ALTER TABLE order_items ADD COLUMN flagged_customer_match_text TEXT NULL', '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', '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', 'SELECT 1'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Statuses, if migration was skipped or partially applied.
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;

SET @new_status_id := (SELECT id FROM production_statuses WHERE status_code='new_order' LIMIT 1);
UPDATE order_items SET current_status_id=@new_status_id WHERE current_status_id IS NULL AND @new_status_id IS NOT NULL;

-- Helpful indexes. Non-unique source index is enough for search; unique transaction index only if it does not break due to old duplicate tests.
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;
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;
