-- Ürün yönetimi: kategori tablosu + ürün ek alanları.
-- Bir kez çalıştırın.
USE faunamix;

CREATE TABLE IF NOT EXISTS product_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sort_order INT NOT NULL DEFAULT 0,
  title VARCHAR(128) NOT NULL,
  slug VARCHAR(64) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_product_categories_slug (slug),
  KEY idx_product_categories_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @has_content_html := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'content_html'
);
SET @sql_content_html := IF(@has_content_html = 0,
  'ALTER TABLE products ADD COLUMN content_html MEDIUMTEXT NULL AFTER description',
  'SELECT 1');
PREPARE stmt FROM @sql_content_html; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_show_on_home := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'show_on_home'
);
SET @sql_show_on_home := IF(@has_show_on_home = 0,
  'ALTER TABLE products ADD COLUMN show_on_home TINYINT(1) NOT NULL DEFAULT 0 AFTER stock_quantity',
  'SELECT 1');
PREPARE stmt FROM @sql_show_on_home; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_is_active := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'is_active'
);
SET @sql_is_active := IF(@has_is_active = 0,
  'ALTER TABLE products ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER show_on_home',
  'SELECT 1');
PREPARE stmt FROM @sql_is_active; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_sort_order := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'sort_order'
);
SET @sql_sort_order := IF(@has_sort_order = 0,
  'ALTER TABLE products ADD COLUMN sort_order INT NOT NULL DEFAULT 0 AFTER id',
  'SELECT 1');
PREPARE stmt FROM @sql_sort_order; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ürün URL slug (şema + admin API p.slug ile uyumlu)
SET @has_slug := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'slug'
);
SET @sql_slug := IF(@has_slug = 0,
  'ALTER TABLE products ADD COLUMN slug VARCHAR(191) NULL',
  'SELECT 1');
PREPARE stmt FROM @sql_slug; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE products SET slug = CONCAT('urun-', id) WHERE slug IS NULL OR TRIM(COALESCE(slug, '')) = '';

SET @has_slug_nn := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'slug'
    AND IS_NULLABLE = 'YES'
);
SET @sql_slug_nn := IF(@has_slug_nn > 0,
  'ALTER TABLE products MODIFY COLUMN slug VARCHAR(191) NOT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql_slug_nn; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_uq_products_slug := (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND INDEX_NAME = 'uq_products_slug'
);
SET @sql_uq_products_slug := IF(@has_uq_products_slug = 0,
  'ALTER TABLE products ADD UNIQUE KEY uq_products_slug (slug)',
  'SELECT 1');
PREPARE stmt FROM @sql_uq_products_slug; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_cat_page_title := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'product_categories' AND COLUMN_NAME = 'page_title'
);
SET @sql_cat_page_title := IF(@has_cat_page_title = 0,
  'ALTER TABLE product_categories ADD COLUMN page_title VARCHAR(255) NOT NULL DEFAULT '''' AFTER title',
  'SELECT 1');
PREPARE stmt FROM @sql_cat_page_title; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_cat_meta_keywords := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'product_categories' AND COLUMN_NAME = 'meta_keywords'
);
SET @sql_cat_meta_keywords := IF(@has_cat_meta_keywords = 0,
  'ALTER TABLE product_categories ADD COLUMN meta_keywords TEXT NULL AFTER page_title',
  'SELECT 1');
PREPARE stmt FROM @sql_cat_meta_keywords; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_cat_meta_description := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'product_categories' AND COLUMN_NAME = 'meta_description'
);
SET @sql_cat_meta_description := IF(@has_cat_meta_description = 0,
  'ALTER TABLE product_categories ADD COLUMN meta_description TEXT NULL AFTER meta_keywords',
  'SELECT 1');
PREPARE stmt FROM @sql_cat_meta_description; EXECUTE stmt; DEALLOCATE PREPARE stmt;

INSERT INTO product_categories (sort_order, title, slug, is_active)
SELECT 0, 'Sürü sağlığı', 'saglik', 1
WHERE NOT EXISTS (SELECT 1 FROM product_categories WHERE slug = 'saglik');

INSERT INTO product_categories (sort_order, title, slug, is_active)
SELECT 1, 'Rasyon & süreç', 'teknoloji', 1
WHERE NOT EXISTS (SELECT 1 FROM product_categories WHERE slug = 'teknoloji');

INSERT INTO product_categories (sort_order, title, slug, is_active)
SELECT 2, 'Performans & verim', 'spor', 1
WHERE NOT EXISTS (SELECT 1 FROM product_categories WHERE slug = 'spor');

UPDATE products
SET show_on_home = 1
WHERE show_on_home = 0;

UPDATE products
SET sort_order = id
WHERE sort_order = 0;

UPDATE product_categories
SET page_title = title
WHERE page_title = '';
