-- MySQL Table Structure for Needly E-commerce Database

-- Users Table (REMOVED - No longer needed for core functionality)
/*
CREATE TABLE `users` (...);
*/

-- Categories Table
CREATE TABLE `categories` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL, -- Will be populated based on category name later
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `categories_slug_unique` (`slug`),
  KEY `categories_parent_id_foreign` (`parent_id`),
  CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products Table
CREATE TABLE `products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `sale_price` decimal(10,2) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `gallery` text DEFAULT NULL,
  `category_id` bigint(20) UNSIGNED NOT NULL,
  `stock` int(11) NOT NULL DEFAULT 0, -- Renamed from quantity in CartController check
  `sku` varchar(255) DEFAULT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `popularity` int(11) NOT NULL DEFAULT 0,
  `tags` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`),
  KEY `products_category_id_foreign` (`category_id`),
  CONSTRAINT `products_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cart Table (Modified for Guest Cart)
-- Renamed from 'carts' to 'cart' as requested by user and reflected in Model
CREATE TABLE `cart` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cart_id` varchar(255) NOT NULL, -- Cookie/Session identifier
  -- `user_id` bigint(20) UNSIGNED DEFAULT NULL, -- Removed user_id
  `product_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cart_cart_id_index` (`cart_id`), -- Renamed index
  -- KEY `carts_user_id_foreign` (`user_id`), -- Removed user_id key
  KEY `cart_product_id_foreign` (`product_id`), -- Renamed key
  CONSTRAINT `cart_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE -- Renamed constraint
  -- CONSTRAINT `carts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE -- Removed user_id constraint
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Love Story Pages Table (No user_id dependency, links to book_id)
CREATE TABLE `love_story_pages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `book_id` bigint(20) UNSIGNED NOT NULL,
  `page_number` int(11) NOT NULL,
  `content` text NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `love_story_pages_book_id_foreign` (`book_id`),
  CONSTRAINT `love_story_pages_book_id_foreign` FOREIGN KEY (`book_id`) REFERENCES `love_story_books` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- AI Chat Messages Table (No direct user_id dependency, links to chat_id)
CREATE TABLE `ai_chat_messages` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` bigint(20) UNSIGNED NOT NULL,
  `sender` enum("user","ai") NOT NULL,
  `message` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ai_chat_messages_chat_id_foreign` (`chat_id`),
  CONSTRAINT `ai_chat_messages_chat_id_foreign` FOREIGN KEY (`chat_id`) REFERENCES `ai_chats` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reviews Table (REMOVED - Requires User Login)
/*
CREATE TABLE `reviews` (...);
*/

-- Wishlists Table (REMOVED - Requires User Login)
/*
CREATE TABLE `wishlists` (...);
*/

-- Couple Accounts Table (REMOVED - Requires User Login)
/*
CREATE TABLE `couple_accounts` (...);
*/

-- Gift Registry Table (REMOVED - Requires User Login)
/*
CREATE TABLE `gift_registries` (...);
*/

-- Gift Registry Items Table (REMOVED - Requires User Login)
/*
CREATE TABLE `gift_registry_items` (...);
*/

-- Coupons Table (Can be kept, applied at cart potentially)
CREATE TABLE `coupons` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  `type` enum("fixed","percentage") NOT NULL,
  `value` decimal(10,2) NOT NULL,
  `min_order_amount` decimal(10,2) DEFAULT NULL,
  `starts_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `usage_limit` int(11) DEFAULT NULL,
  `used_count` int(11) NOT NULL DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `coupons_code_unique` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Newsletter Subscribers Table (Can be kept)
CREATE TABLE `newsletter_subscribers` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `newsletter_subscribers_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample Data Insertion for Categories (Updated with image paths)
INSERT INTO `categories` (`id`, `name`, `slug`, `description`, `image`, `parent_id`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Gifts', 'gifts', 'Unique and thoughtful gifts for your loved ones', '/images/categories/gifts.png', NULL, 1, NOW(), NOW()),
(2, 'Jewelry', 'jewelry', 'Beautiful jewelry pieces to express your love', '/images/categories/jewelry.png', NULL, 1, NOW(), NOW()),
(3, 'Couple Items', 'couple-items', 'Special items designed for couples', '/images/categories/couple-items.png', NULL, 1, NOW(), NOW()),
(4, 'Love Story Books', 'love-story-books', 'Personalized books to tell your love story', '/images/categories/love-story-books.png', NULL, 1, NOW(), NOW()),
(5, 'Romantic Experiences', 'romantic-experiences', 'Create unforgettable memories together', '/images/categories/romantic-experiences.png', NULL, 1, NOW(), NOW());

-- Sample Data Insertion for Products
INSERT INTO `products` (`name`, `slug`, `description`, `price`, `sale_price`, `category_id`, `stock`, `featured`, `status`, `popularity`, `tags`, `created_at`, `updated_at`) VALUES
("Personalized Love Story Book", "personalized-love-story-book", "A beautiful custom book that tells your unique love story with personalized pages and photos.", 49.99, 39.99, 4, 100, 1, 1, 10, "book,love,story,personalized,gift", NOW(), NOW()),
("Couple Matching Bracelets", "couple-matching-bracelets", "Elegant matching bracelets for couples, made with high-quality materials and customizable with your initials.", 29.99, NULL, 3, 150, 1, 1, 8, "bracelet,couple,matching,jewelry", NOW(), NOW()),
("Heart-Shaped Necklace", "heart-shaped-necklace", "Beautiful heart-shaped pendant necklace made with sterling silver and cubic zirconia.", 59.99, 49.99, 2, 75, 1, 1, 15, "necklace,heart,jewelry,gift", NOW(), NOW()),
("Romantic Dinner Experience", "romantic-dinner-experience", "Gift a memorable romantic dinner experience at a top restaurant with a special menu and ambiance.", 99.99, NULL, 5, 50, 1, 1, 5, "experience,dinner,romantic,date", NOW(), NOW()),
("Custom Photo Frame", "custom-photo-frame", "Elegant wooden photo frame that can be personalized with names, dates, and a special message.", 34.99, 29.99, 1, 120, 0, 1, 7, "frame,photo,custom,gift", NOW(), NOW()),
("Love Letter Bracelet", "love-letter-bracelet", "A delicate bracelet with a small envelope charm that opens to reveal a tiny love note.", 24.99, NULL, 2, 100, 0, 1, 6, "bracelet,letter,love,jewelry", NOW(), NOW()),
("Couple Memory Jar", "couple-memory-jar", "A beautiful jar with 365 blank papers to write memories, quotes, or reasons why you love your partner.", 19.99, 17.99, 3, 200, 0, 1, 9, "jar,memory,couple,gift", NOW(), NOW()),
("Star Map Print", "star-map-print", "A custom map of the stars on a specific date and location, perfect for anniversaries or special moments.", 45.00, NULL, 1, 80, 0, 1, 12, "star,map,print,custom,gift,anniversary", NOW(), NOW());

