-- =====================================================
-- Database Cleanup Script for Production Deployment
-- =====================================================
-- This script removes all products, orders, and related test data
-- while keeping essential configuration data (categories, settings, etc.)
--
-- WARNING: This will permanently delete data. Backup your database first!
-- =====================================================

-- Disable foreign key checks temporarily

-- =====================================================
-- 1. DELETE ORDERS AND RELATED DATA
-- =====================================================
TRUNCATE TABLE `order_items`;
TRUNCATE TABLE `shipments`; -- If table exists
TRUNCATE TABLE `orders`;

-- =====================================================
-- 2. DELETE PRODUCTS AND RELATED DATA
-- =====================================================
-- NOTE: This script only deletes database records.
-- Product images in storage/app/public/products/ are NOT deleted.
-- To delete images, manually remove the files or use the Laravel command.
-- Delete product variant attribute values
TRUNCATE TABLE `product_variant_attribute_values`;

-- Delete variant stock movements
TRUNCATE TABLE `variant_stock_movements`;

-- Delete product variants
TRUNCATE TABLE `product_variants`;

-- Delete product media
TRUNCATE TABLE `product_media`;

-- Delete product stock movements
TRUNCATE TABLE `product_stock_movements`;

-- Delete products (this will reset auto-increment)
TRUNCATE TABLE `products`;

-- Reset product rating fields (in case table still exists)
UPDATE `products` SET `avg_rating` = 0, `rating_count` = 0;

-- =====================================================
-- 3. DELETE CARTS AND CART ITEMS
-- =====================================================
TRUNCATE TABLE `cart_items`;
TRUNCATE TABLE `carts`;

-- =====================================================
-- 4. DELETE WISHLISTS
-- =====================================================
TRUNCATE TABLE `wishlists`;

-- =====================================================
-- 5. DELETE PRODUCT REVIEWS
-- =====================================================
TRUNCATE TABLE `product_reviews`;

-- =====================================================
-- 6. DELETE STOCK MOVEMENTS
-- =====================================================
TRUNCATE TABLE `product_stock_movements`;
TRUNCATE TABLE `variant_stock_movements`;

-- =====================================================
-- 7. DELETE STOCK RESERVATIONS
-- =====================================================
TRUNCATE TABLE `stock_reservations`;

-- =====================================================
-- 8. DELETE OLD OTP CODES (older than 1 hour)
-- =====================================================
DELETE FROM `otp_codes` WHERE `created_at` < DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- =====================================================
-- 9. DELETE COUPON REDEMPTIONS (Optional - uncomment if needed)
-- =====================================================
-- TRUNCATE TABLE `coupon_redemptions`;

-- =====================================================
-- 10. DELETE CUSTOMER USERS (Optional - uncomment if needed)
-- Keeps admin and staff users
-- =====================================================
-- DELETE FROM `users` WHERE `user_type` = 'customer' OR `user_type` IS NULL;

-- =====================================================
-- 11. DELETE CUSTOMER ADDRESSES (Optional - uncomment if needed)
-- =====================================================
-- TRUNCATE TABLE `addresses`;

-- =====================================================
-- 12. RESET AUTO_INCREMENT COUNTERS (Optional)
-- =====================================================
-- Uncomment these if you want to reset auto-increment IDs
-- ALTER TABLE `orders` AUTO_INCREMENT = 1;
-- ALTER TABLE `order_items` AUTO_INCREMENT = 1;
-- ALTER TABLE `products` AUTO_INCREMENT = 1;
-- ALTER TABLE `product_variants` AUTO_INCREMENT = 1;
-- ALTER TABLE `carts` AUTO_INCREMENT = 1;
-- ALTER TABLE `cart_items` AUTO_INCREMENT = 1;
-- ALTER TABLE `wishlists` AUTO_INCREMENT = 1;
-- ALTER TABLE `product_reviews` AUTO_INCREMENT = 1;

-- Re-enable foreign key checks

-- =====================================================
-- VERIFICATION QUERIES (Run these to verify cleanup)
-- =====================================================
-- SELECT COUNT(*) as order_count FROM `orders`;
-- SELECT COUNT(*) as product_count FROM `products`;
-- SELECT COUNT(*) as cart_count FROM `carts`;
-- SELECT COUNT(*) as wishlist_count FROM `wishlists`;
-- SELECT COUNT(*) as review_count FROM `product_reviews`;

-- =====================================================
-- DATA THAT IS KEPT (Not deleted)
-- =====================================================
-- ✅ Categories (structure and hierarchy)
-- ✅ Settings (site configuration)
-- ✅ Users (admin and staff - if customer deletion is commented)
-- ✅ Shipping Methods
-- ✅ Tax Settings
-- ✅ Banners
-- ✅ Announcements
-- ✅ Brands
-- ✅ Attributes and Attribute Values
-- ✅ Coupons (if redemptions deletion is commented)
-- ✅ Units
-- ✅ Addresses (if deletion is commented)
-- ✅ Permissions and Roles

-- =====================================================
-- END OF SCRIPT
-- =====================================================

