-- ==============================================================================
-- PETMARKETIM.NET - PET5 ENTERPRISE MIGRATION SCRIPT (MARIADB / MYSQL COMPATIBLE)
-- ==============================================================================

-- 1. CATEGORIES TABLE UPDATES
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `parent_id` INT DEFAULT NULL;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `description` TEXT DEFAULT NULL;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `image` TEXT DEFAULT NULL;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `banner_image` TEXT DEFAULT NULL;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `display_order` INT NOT NULL DEFAULT 0;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `status` VARCHAR(32) NOT NULL DEFAULT 'active';
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `seo_title` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `seo_description` TEXT DEFAULT NULL;

-- 2. BRANDS TABLE UPDATES
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `logo` TEXT DEFAULT NULL;
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `description` TEXT DEFAULT NULL;
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `website` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `status` VARCHAR(32) NOT NULL DEFAULT 'active';
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `seo_title` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `brands` ADD COLUMN IF NOT EXISTS `seo_description` TEXT DEFAULT NULL;

-- 3. PRODUCTS TABLE UPDATES
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `sale_price` INT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `regular_price` INT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `cost_price` INT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `tax_rate` DOUBLE NOT NULL DEFAULT 20.0;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `currency` VARCHAR(10) NOT NULL DEFAULT 'TRY';
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `stock_status` VARCHAR(32) NOT NULL DEFAULT 'in_stock';
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `manage_stock` TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `low_stock_threshold` INT NOT NULL DEFAULT 5;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `images` JSON DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `weight` DOUBLE NOT NULL DEFAULT 0.0;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `dimensions` JSON DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `secondary_category_ids` JSON DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `supplier_id` INT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `supplier_sku` VARCHAR(128) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `sku` VARCHAR(128) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `barcode` VARCHAR(128) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `ean` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `mpn` VARCHAR(128) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `model_code` VARCHAR(128) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `short_description` TEXT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `video_url` VARCHAR(512) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `attributes` JSON DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `has_variants` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `status` VARCHAR(32) NOT NULL DEFAULT 'active';
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `visibility` VARCHAR(32) NOT NULL DEFAULT 'visible';
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `seo_title` VARCHAR(255) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `seo_description` TEXT DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `canonical_url` VARCHAR(512) DEFAULT NULL;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `robots_index` TINYINT(1) NOT NULL DEFAULT 1;
ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `published_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- 4. ORDERS TABLE UPDATES
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `discount` INT NOT NULL DEFAULT 0;
ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `coupon_code` VARCHAR(64) DEFAULT NULL;

-- 5. PRODUCT VARIANTS TABLE
CREATE TABLE IF NOT EXISTS `product_variants` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `sku` VARCHAR(128) NOT NULL,
  `barcode` VARCHAR(128) DEFAULT NULL,
  `title` VARCHAR(255) DEFAULT NULL,
  `price` INT NOT NULL,
  `sale_price` INT DEFAULT NULL,
  `stock` INT NOT NULL DEFAULT 0,
  `attributes` JSON DEFAULT NULL,
  `image` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. SUPPLIERS TABLE
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `code` VARCHAR(64) NOT NULL UNIQUE,
  `feed_url` TEXT DEFAULT NULL,
  `feed_type` VARCHAR(32) NOT NULL DEFAULT 'xml',
  `sync_frequency_minutes` INT NOT NULL DEFAULT 360,
  `margin_type` VARCHAR(32) NOT NULL DEFAULT 'percentage',
  `margin_value` DOUBLE NOT NULL DEFAULT 20.0,
  `stock_rule` JSON DEFAULT NULL,
  `auto_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_sync_at` TIMESTAMP NULL DEFAULT NULL,
  `last_sync_status` VARCHAR(32) NOT NULL DEFAULT 'idle',
  `last_sync_errors` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. COUPONS TABLE
CREATE TABLE IF NOT EXISTS `coupons` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(64) NOT NULL UNIQUE,
  `type` VARCHAR(32) NOT NULL DEFAULT 'percentage',
  `amount` INT NOT NULL DEFAULT 0,
  `min_spend` INT NOT NULL DEFAULT 0,
  `max_spend` INT DEFAULT NULL,
  `usage_limit` INT DEFAULT NULL,
  `used_count` INT NOT NULL DEFAULT 0,
  `start_date` TIMESTAMP NULL DEFAULT NULL,
  `end_date` TIMESTAMP NULL DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. SHIPPING RULES TABLE
CREATE TABLE IF NOT EXISTS `shipping_rules` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `carrier` VARCHAR(64) NOT NULL DEFAULT 'aras',
  `min_amount_free_shipping` INT NOT NULL DEFAULT 150000,
  `fixed_price` INT NOT NULL DEFAULT 4900,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. RETURNS TABLE
CREATE TABLE IF NOT EXISTS `returns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `return_no` VARCHAR(64) NOT NULL UNIQUE,
  `order_id` INT NOT NULL,
  `order_no` VARCHAR(64) NOT NULL,
  `customer_id` INT DEFAULT NULL,
  `items` JSON NOT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'requested',
  `reason` VARCHAR(255) NOT NULL,
  `refund_amount` INT NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `tracking_number` VARCHAR(128) DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. AUDIT LOGS TABLE
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `actor` VARCHAR(128) NOT NULL DEFAULT 'chatgpt-pet5',
  `source` VARCHAR(64) NOT NULL DEFAULT 'mcp',
  `action` VARCHAR(128) NOT NULL,
  `entity` VARCHAR(64) NOT NULL,
  `entity_id` VARCHAR(128) DEFAULT NULL,
  `changes` JSON DEFAULT NULL,
  `ip_address` VARCHAR(64) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. REDIRECTS TABLE
CREATE TABLE IF NOT EXISTS `redirects` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `from_path` VARCHAR(512) NOT NULL UNIQUE,
  `to_path` VARCHAR(512) NOT NULL,
  `status_code` INT NOT NULL DEFAULT 301,
  `hits` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. MARKETING ATTRIBUTIONS TABLE
CREATE TABLE IF NOT EXISTS `marketing_attributions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT DEFAULT NULL,
  `order_no` VARCHAR(64) DEFAULT NULL,
  `utm_source` VARCHAR(128) DEFAULT NULL,
  `utm_medium` VARCHAR(128) DEFAULT NULL,
  `utm_campaign` VARCHAR(128) DEFAULT NULL,
  `utm_term` VARCHAR(128) DEFAULT NULL,
  `utm_content` VARCHAR(128) DEFAULT NULL,
  `gclid` VARCHAR(255) DEFAULT NULL,
  `fbclid` VARCHAR(255) DEFAULT NULL,
  `referrer` TEXT DEFAULT NULL,
  `landing_page` TEXT DEFAULT NULL,
  `event_details` JSON DEFAULT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. PAGES TABLE
CREATE TABLE IF NOT EXISTS `pages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `template` VARCHAR(64) NOT NULL DEFAULT 'default',
  `status` VARCHAR(32) NOT NULL DEFAULT 'published',
  `seo_title` VARCHAR(255) DEFAULT NULL,
  `seo_description` TEXT DEFAULT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. BLOG POSTS TABLE
CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `title` VARCHAR(255) NOT NULL,
  `excerpt` TEXT DEFAULT NULL,
  `content` TEXT NOT NULL,
  `featured_image` TEXT DEFAULT NULL,
  `status` VARCHAR(32) NOT NULL DEFAULT 'published',
  `tags` VARCHAR(255) DEFAULT NULL,
  `seo_title` VARCHAR(255) DEFAULT NULL,
  `seo_description` TEXT DEFAULT NULL,
  `published_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;