-- =======================================================
-- Arti Atolye ERP — Patch V5 SQL Migration
-- CSV Preview + Admin CSV Approval yeniden tasarım için
-- IDEMPOTENT: Tekrar çalıştırılabilir, veri silmez.
-- =======================================================

SET NAMES utf8mb4;

-- -------------------------------------------------------
-- Yardımcı prosedür: kolon yoksa ekle
-- -------------------------------------------------------
DROP PROCEDURE IF EXISTS erp_add_col;
DELIMITER $$
CREATE PROCEDURE erp_add_col(
  IN p_table  VARCHAR(128),
  IN p_col    VARCHAR(128),
  IN p_def    TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = p_table
      AND COLUMN_NAME  = p_col
  ) THEN
    SET @_ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_col, '` ', p_def);
    PREPARE _s FROM @_ddl; EXECUTE _s; DEALLOCATE PREPARE _s;
  END IF;
END$$
DELIMITER ;

-- -------------------------------------------------------
-- erp_counters (üretim sıra no tablosu)
-- -------------------------------------------------------
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;

-- Üretim sıra no 10001'den başlar — INSERT IGNORE: varsa dokunma
INSERT IGNORE INTO `erp_counters` (`counter_key`, `next_value`, `updated_at`)
VALUES ('production_no', 10001, NOW());

-- -------------------------------------------------------
-- csv_import_rows eksik kolonlar
-- -------------------------------------------------------
CALL erp_add_col('csv_import_rows', 'net_item_total',
  'decimal(12,2) DEFAULT NULL AFTER `discount_amount`');

CALL erp_add_col('csv_import_rows', 'shipping_type',
  'varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_sales_tax`');

CALL erp_add_col('csv_import_rows', 'order_group_key',
  'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `etsy_order_id`');

CALL erp_add_col('csv_import_rows', 'customer_group_key',
  'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_group_key`');

CALL erp_add_col('csv_import_rows', 'flag_match_type',
  'varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `flag_public_text`');

-- -------------------------------------------------------
-- order_items eksik kolonlar
-- NOT: product_image_path eksik olduğu için orders.php,
--      production.php, order_detail.php crash yapıyor.
--      Bu kolon eklendikten sonra o sayfalar da düzelir.
-- -------------------------------------------------------
CALL erp_add_col('order_items', 'production_no',
  'int UNSIGNED DEFAULT NULL AFTER `id`');

CALL erp_add_col('order_items', 'source_transaction_id',
  'varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `transaction_id`');

CALL erp_add_col('order_items', 'net_item_total',
  'decimal(12,2) DEFAULT NULL AFTER `discount_amount`');

CALL erp_add_col('order_items', 'order_shipping',
  'decimal(12,2) DEFAULT NULL AFTER `shipping_discount`');

CALL erp_add_col('order_items', 'order_sales_tax',
  'decimal(12,2) DEFAULT NULL AFTER `order_shipping`');

CALL erp_add_col('order_items', 'shipping_type',
  'varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_sales_tax`');

CALL erp_add_col('order_items', 'order_group_key',
  'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `etsy_order_id`');

CALL erp_add_col('order_items', 'customer_group_key',
  'varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `order_group_key`');

CALL erp_add_col('order_items', 'quantity_unit_index',
  'int UNSIGNED NOT NULL DEFAULT 1 AFTER `quantity`');

CALL erp_add_col('order_items', 'quantity_unit_total',
  'int UNSIGNED NOT NULL DEFAULT 1 AFTER `quantity_unit_index`');

-- !! KRİTİK: Bu kolon olmadığı için orders.php crash yapıyor
CALL erp_add_col('order_items', 'product_image_path',
  'varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `listing_title`');

CALL erp_add_col('order_items', 'flag_match_type',
  'varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER `flagged_customer_match_text`');

CALL erp_add_col('order_items', 'store_note',
  'text COLLATE utf8mb4_unicode_ci AFTER `raw_csv_json`');

CALL erp_add_col('order_items', 'admin_note',
  'text COLLATE utf8mb4_unicode_ci AFTER `store_note`');

CALL erp_add_col('order_items', 'cancelled_at',
  'datetime DEFAULT NULL AFTER `admin_note`');

CALL erp_add_col('order_items', 'cancelled_by',
  'int UNSIGNED DEFAULT NULL AFTER `cancelled_at`');

CALL erp_add_col('order_items', 'cancel_reason',
  'text COLLATE utf8mb4_unicode_ci AFTER `cancelled_by`');

CALL erp_add_col('order_items', 'shipped_date',
  'date DEFAULT NULL AFTER `ship_by_date`');

-- -------------------------------------------------------
-- İndeksler (varsa tekrar ekleme)
-- -------------------------------------------------------
DROP PROCEDURE IF EXISTS erp_add_idx;
DELIMITER $$
CREATE PROCEDURE erp_add_idx(
  IN p_table VARCHAR(128),
  IN p_idx   VARCHAR(128),
  IN p_def   TEXT
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME   = p_table
      AND INDEX_NAME   = p_idx
  ) THEN
    SET @_ddl = CONCAT('ALTER TABLE `', p_table, '` ADD ', p_def);
    PREPARE _s FROM @_ddl; EXECUTE _s; DEALLOCATE PREPARE _s;
  END IF;
END$$
DELIMITER ;

CALL erp_add_idx('csv_import_rows', 'idx_cir_order_group',
  'KEY `idx_cir_order_group` (`order_group_key`)');
CALL erp_add_idx('csv_import_rows', 'idx_cir_customer_group',
  'KEY `idx_cir_customer_group` (`customer_group_key`)');
CALL erp_add_idx('order_items', 'idx_oi_production_no',
  'KEY `idx_oi_production_no` (`production_no`)');
CALL erp_add_idx('order_items', 'idx_oi_status_ship',
  'KEY `idx_oi_status_ship` (`current_status_id`, `ship_by_date`)');
CALL erp_add_idx('order_items', 'idx_oi_store_status',
  'KEY `idx_oi_store_status` (`store_id`, `current_status_id`)');

-- -------------------------------------------------------
-- Üretim durumları (ON DUPLICATE KEY → güvenli)
-- -------------------------------------------------------
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;

-- -------------------------------------------------------
-- Temizlik
-- -------------------------------------------------------
DROP PROCEDURE IF EXISTS erp_add_col;
DROP PROCEDURE IF EXISTS erp_add_idx;

SELECT 'Patch V5 SQL tamamlandı.' AS result;
