-- arti_erp_operations_csv_v4_PATCH_1
-- SQL FIRST: phpMyAdmin > nbkartia_erp001 > Import
-- Bu dosya veri silmez. Sadece operasyon/CSV v4 için gerekli alanları ekler.
-- Not: Google Sheets sıra no devamı için başlangıç 10001 olarak ayarlandı.

SET @production_no_start := 10001;

CREATE TABLE IF NOT EXISTS `erp_counters` (
  `counter_key` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `next_value` int UNSIGNED NOT NULL DEFAULT 1,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`counter_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP PROCEDURE IF EXISTS add_column_if_missing;
DELIMITER $$
CREATE PROCEDURE add_column_if_missing(
  IN p_table VARCHAR(128),
  IN p_column VARCHAR(128),
  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 @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL add_column_if_missing('csv_import_rows', 'net_item_total', 'decimal(12,2) DEFAULT NULL AFTER `discount_amount`');
CALL add_column_if_missing('csv_import_rows', 'shipping_type', 'varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_sales_tax`');
CALL add_column_if_missing('csv_import_rows', 'order_group_key', 'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `etsy_order_id`');
CALL add_column_if_missing('csv_import_rows', 'customer_group_key', 'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_group_key`');
CALL add_column_if_missing('csv_import_rows', 'flag_match_type', 'varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `flag_public_text`');

CALL add_column_if_missing('order_items', 'production_no', 'int UNSIGNED DEFAULT NULL AFTER `id`');
CALL add_column_if_missing('order_items', 'net_item_total', 'decimal(12,2) DEFAULT NULL AFTER `discount_amount`');
CALL add_column_if_missing('order_items', 'order_shipping', 'decimal(12,2) DEFAULT NULL AFTER `shipping_discount`');
CALL add_column_if_missing('order_items', 'order_sales_tax', 'decimal(12,2) DEFAULT NULL AFTER `order_shipping`');
CALL add_column_if_missing('order_items', 'shipping_type', 'varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_sales_tax`');
CALL add_column_if_missing('order_items', 'order_group_key', 'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `etsy_order_id`');
CALL add_column_if_missing('order_items', 'customer_group_key', 'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_group_key`');
CALL add_column_if_missing('order_items', 'flag_match_type', 'varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `flagged_customer_match_text`');
CALL add_column_if_missing('order_items', 'cancelled_at', 'datetime DEFAULT NULL AFTER `admin_note`');
CALL add_column_if_missing('order_items', 'cancelled_by', 'int UNSIGNED DEFAULT NULL AFTER `cancelled_at`');
CALL add_column_if_missing('order_items', 'cancel_reason', 'text COLLATE utf8mb4_unicode_ci AFTER `cancelled_by`');

DROP PROCEDURE IF EXISTS add_column_if_missing;

DROP PROCEDURE IF EXISTS add_index_if_missing;
DELIMITER $$
CREATE PROCEDURE add_index_if_missing(
  IN p_table VARCHAR(128),
  IN p_index VARCHAR(128),
  IN p_definition TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD ', p_definition);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL add_index_if_missing('csv_import_rows', 'idx_csv_rows_order_group', 'KEY `idx_csv_rows_order_group` (`order_group_key`)');
CALL add_index_if_missing('csv_import_rows', 'idx_csv_rows_customer_group', 'KEY `idx_csv_rows_customer_group` (`customer_group_key`)');
CALL add_index_if_missing('csv_import_rows', 'idx_csv_rows_shipping_type', 'KEY `idx_csv_rows_shipping_type` (`shipping_type`)');
CALL add_index_if_missing('csv_import_rows', 'idx_csv_rows_flag_match_type', 'KEY `idx_csv_rows_flag_match_type` (`flag_match_type`)');

CALL add_index_if_missing('order_items', 'uq_order_items_production_no', 'UNIQUE KEY `uq_order_items_production_no` (`production_no`)');
CALL add_index_if_missing('order_items', 'idx_order_items_order_group', 'KEY `idx_order_items_order_group` (`order_group_key`)');
CALL add_index_if_missing('order_items', 'idx_order_items_customer_group', 'KEY `idx_order_items_customer_group` (`customer_group_key`)');
CALL add_index_if_missing('order_items', 'idx_order_items_shipping_type', 'KEY `idx_order_items_shipping_type` (`shipping_type`)');

DROP PROCEDURE IF EXISTS add_index_if_missing;

INSERT INTO `erp_counters` (`counter_key`, `next_value`, `updated_at`)
SELECT 'production_no', GREATEST(
    @production_no_start,
    COALESCE((SELECT MAX(`production_no`) + 1 FROM `order_items` WHERE `production_no` IS NOT NULL), 1)
), NOW()
ON DUPLICATE KEY UPDATE
  `next_value` = GREATEST(`next_value`, VALUES(`next_value`)),
  `updated_at` = NOW();
