--Manish 5-2-26
ALTER TABLE `users` ADD `address` TEXT NULL DEFAULT NULL AFTER `image`;
ALTER TABLE `users` CHANGE `status` `status` TINYINT NULL;

--Manish 16-2-26
ALTER TABLE `products` ADD `title` TEXT NULL DEFAULT NULL AFTER `slug`;
ALTER TABLE `products` ADD `description` TEXT NULL DEFAULT NULL AFTER `tital`;

--Manish 21-2-26
ALTER TABLE `products` CHANGE `name` `name` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `products` CHANGE `slug` `slug` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `products` CHANGE `list_image` `list_image` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `products_images` CHANGE `product_image` `product_image` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

--Manish 24-2-26
ALTER TABLE `products` ADD UNIQUE(`slug`);
ALTER TABLE `enquiries` ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `product_id`;
ALTER TABLE `categories` CHANGE `name` `name` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `categories` CHANGE `slug` `slug` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;


--Manish 25-2-26
ALTER TABLE `roles` ADD `is_active` TINYINT NOT NULL DEFAULT '1' AFTER `deleted_at`;
ALTER TABLE `products` ADD UNIQUE(`name`);
ALTER TABLE `categories` ADD UNIQUE(`name`);

--Manish 26-2-26
ALTER TABLE `products` ADD `inventory_id` VARCHAR(255) NOT NULL AFTER `id`;
ALTER TABLE `products` ADD UNIQUE(`inventory_id`);

ALTER TABLE `products` ADD `sku` VARCHAR(255) NOT NULL AFTER `name`, ADD UNIQUE (`sku`);
ALTER TABLE `products` CHANGE `inventory_id` `inventory_id` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

ALTER TABLE `products` ADD `sub_colors` JSON NULL DEFAULT NULL AFTER `color`;
ALTER TABLE `products` ADD `price_per_sqft` DECIMAL NULL DEFAULT '0' AFTER `sale_price`;
ALTER TABLE `products` CHANGE `price_per_sqft` `price_per_sqft` DECIMAL(10,2) NULL DEFAULT '0';
ALTER TABLE `products` ADD `piece_per_box` INT NOT NULL DEFAULT '0' AFTER `price_per_sqft`;

--Manish 28-2-26
ALTER TABLE `enquiries` ADD `project_type` VARCHAR(255) NULL DEFAULT NULL AFTER `location`;

--Manish 6-3-26
UPDATE categories c
LEFT JOIN categories p ON c.parent_id = p.id
LEFT JOIN categories gp ON p.parent_id = gp.id
SET c.section = 
    CASE 
        WHEN c.parent_id IS NULL THEN NULL
        WHEN p.parent_id IS NULL THEN p.name
        ELSE gp.name
    END;

--Manish 7-3-26
ALTER TABLE `categories` ADD `trending` TINYINT NOT NULL DEFAULT '0' AFTER `icon_url`;
-- one new table add book appointments

--Manish 10-3-26
--leads table
ALTER TABLE `products` CHANGE `piece_per_box` `piece_per_box` INT(11) NULL DEFAULT '0';

--Manish 18-3-26
ALTER TABLE `gallery` CHANGE `room_type` `application` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `gallery` CHANGE `design_type` `category` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `gallery` CHANGE `application` `application` INT NULL DEFAULT NULL;
ALTER TABLE `gallery` CHANGE `category` `category` INT NULL DEFAULT NULL;


--Manish 19-3-26
ALTER TABLE `enquiries` ADD `source_url` TEXT NULL DEFAULT NULL AFTER `area`;
-- 
ALTER TABLE `enquiries` ADD `status` VARCHAR(255) NULL DEFAULT 'Pending' AFTER `area`;
ALTER TABLE `enquiries` ADD `priority` VARCHAR(255) NULL DEFAULT 'Cold' AFTER `status`;
ALTER TABLE `leads` CHANGE `status` `status` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'Qualify';

--Rahul 24-6-26
ALTER TABLE customers ADD plain_password VARCHAR(255) NULL;

-- Rahul 25-6-26 (Wishlist & Wishlist Items tables)
CREATE TABLE IF NOT EXISTS `wishlists` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: If wishlists table already existed with product_id, run these instead:
-- ALTER TABLE `wishlists` ADD `name` VARCHAR(255) NOT NULL AFTER `user_id`;
-- ALTER TABLE `wishlists` DROP COLUMN `product_id`;

CREATE TABLE IF NOT EXISTS `wishlist_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `wishlist_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `wishlist_items_wishlist_id_foreign` (`wishlist_id`),
  CONSTRAINT `wishlist_items_wishlist_id_foreign` FOREIGN KEY (`wishlist_id`) REFERENCES `wishlists` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rahul 25-6-26 (Add retainable column to quotations table)
ALTER TABLE `quotations` ADD `retainable` VARCHAR(255) NULL DEFAULT 'retainable' AFTER `project_type`;

-- Rahul 25-6-26 (Add code column to quotations table)
ALTER TABLE `quotations` ADD `code` VARCHAR(255) NULL AFTER `id`;
ALTER TABLE `quotations` ADD UNIQUE(`code`);

-- Manish 14-7-26
ALTER TABLE `categories` ADD `category_code` VARCHAR(55) NULL DEFAULT NULL AFTER `parent_id`;
ALTER TABLE `products` ADD `company_code` VARCHAR(55) NULL DEFAULT NULL AFTER `inventory_id`;
ALTER TABLE `products` CHANGE `unit` `unit` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

-- 20-7-26 (Company Master Table)
CREATE TABLE IF NOT EXISTS `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `company_code` varchar(255) NOT NULL,
  `technical_specification` longtext DEFAULT NULL,
  `packaging_detail` longtext DEFAULT NULL,
  `attributes` json DEFAULT 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 `companies_company_code_unique` (`company_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;