-- =====================================================================
-- Boyz Fashion: live database update (14-17 Oct 2026 er notun feature)
--   * products.color_codes            (color picker)
--   * users.orders_seen_at            (Orders badge)
--   * landing_pages table             (Landing Pages)
--   * order_items.size / color        (order e size, color)
--   * orders.landing_page_id          (kon landing page theke order)
--   * orders.courier ... courier_sent_at (courier e pathano, tracking)
--
-- phpMyAdmin > live database select > SQL tab > pura file paste > Go.
-- Shudhu je column/table NEI sheigula add hoy; duibar chalaleo kono error nei.
-- Kono purono data muche na. Tobuo chalanor AGE Export diye backup nao.
-- =====================================================================

SET NAMES utf8mb4;
SET @db = DATABASE();

-- ---------------------------------------------------------------------
-- 1. products.color_codes
-- ---------------------------------------------------------------------
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'products' AND COLUMN_NAME = 'color_codes') = 0,
    'ALTER TABLE `products` ADD COLUMN `color_codes` JSON NULL AFTER `colors`',
    'SELECT ''products.color_codes: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
-- 2. users.orders_seen_at
-- ---------------------------------------------------------------------
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'users' AND COLUMN_NAME = 'orders_seen_at') = 0,
    'ALTER TABLE `users` ADD COLUMN `orders_seen_at` TIMESTAMP NULL DEFAULT NULL AFTER `is_admin`',
    'SELECT ''users.orders_seen_at: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
-- 3. landing_pages table (na thakle toiri)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `landing_pages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `offer_price` DECIMAL(10,2) NULL DEFAULT NULL,
  `free_shipping` TINYINT(1) NOT NULL DEFAULT 0,
  `theme_color` VARCHAR(20) NOT NULL DEFAULT '#0b2d6b',
  `accent_color` VARCHAR(20) NOT NULL DEFAULT '#ffb400',
  `badge_text` VARCHAR(255) NULL DEFAULT NULL,
  `headline` VARCHAR(255) NOT NULL,
  `subheadline` VARCHAR(500) NULL DEFAULT NULL,
  `hero_image` VARCHAR(255) NULL DEFAULT NULL,
  `video_url` VARCHAR(255) NULL DEFAULT NULL,
  `button_text` VARCHAR(255) NOT NULL DEFAULT 'এখনই অর্ডার করুন',
  `countdown_end` DATETIME NULL DEFAULT NULL,
  `features` JSON NULL,
  `reviews` JSON NULL,
  `faqs` JSON NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `landing_pages_slug_unique` (`slug`),
  KEY `landing_pages_product_id_foreign` (`product_id`),
  CONSTRAINT `landing_pages_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. order_items.size, order_items.color
-- ---------------------------------------------------------------------
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'size') = 0,
    'ALTER TABLE `order_items` ADD COLUMN `size` VARCHAR(50) NULL DEFAULT NULL AFTER `name`',
    'SELECT ''order_items.size: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'order_items' AND COLUMN_NAME = 'color') = 0,
    'ALTER TABLE `order_items` ADD COLUMN `color` VARCHAR(50) NULL DEFAULT NULL AFTER `size`',
    'SELECT ''order_items.color: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
-- 5. orders.landing_page_id (+ foreign key)
-- ---------------------------------------------------------------------
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'landing_page_id') = 0,
    'ALTER TABLE `orders` ADD COLUMN `landing_page_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `coupon_code`',
    'SELECT ''orders.landing_page_id: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND CONSTRAINT_NAME = 'orders_landing_page_id_foreign') = 0,
    'ALTER TABLE `orders` ADD CONSTRAINT `orders_landing_page_id_foreign` FOREIGN KEY (`landing_page_id`) REFERENCES `landing_pages` (`id`) ON DELETE SET NULL',
    'SELECT ''orders_landing_page_id_foreign: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
-- 6. orders: courier, consignment_id, tracking_code, courier_status, courier_sent_at
-- ---------------------------------------------------------------------
SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'courier') = 0,
    'ALTER TABLE `orders` ADD COLUMN `courier` VARCHAR(30) NULL DEFAULT NULL AFTER `landing_page_id`',
    'SELECT ''orders.courier: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'consignment_id') = 0,
    'ALTER TABLE `orders` ADD COLUMN `consignment_id` VARCHAR(100) NULL DEFAULT NULL AFTER `courier`',
    'SELECT ''orders.consignment_id: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'tracking_code') = 0,
    'ALTER TABLE `orders` ADD COLUMN `tracking_code` VARCHAR(100) NULL DEFAULT NULL AFTER `consignment_id`',
    'SELECT ''orders.tracking_code: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'courier_status') = 0,
    'ALTER TABLE `orders` ADD COLUMN `courier_status` VARCHAR(100) NULL DEFAULT NULL AFTER `tracking_code`',
    'SELECT ''orders.courier_status: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @s = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'courier_sent_at') = 0,
    'ALTER TABLE `orders` ADD COLUMN `courier_sent_at` TIMESTAMP NULL DEFAULT NULL AFTER `courier_status`',
    'SELECT ''orders.courier_sent_at: already ache'' AS info');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ---------------------------------------------------------------------
-- 7. Laravel ke janano je ei migration gula hoye geche
--    (na hole admin er "Clear cache & update database" abar chalate giye error dibe)
-- ---------------------------------------------------------------------
SET @batch = (SELECT IFNULL(MAX(`batch`), 0) + 1 FROM `migrations`);

INSERT INTO `migrations` (`migration`, `batch`)
SELECT m.name, @batch FROM (
    SELECT '2026_10_14_000001_add_color_codes_to_products' AS name
    UNION ALL SELECT '2026_10_15_000001_add_orders_seen_at_to_users'
    UNION ALL SELECT '2026_10_15_000002_create_landing_pages_table'
    UNION ALL SELECT '2026_10_17_000001_add_courier_fields_to_orders'
) AS m
WHERE m.name NOT IN (SELECT `migration` FROM (SELECT `migration` FROM `migrations`) AS done);

SELECT 'Done: database update shesh' AS result;