-- Office Dairy - Database Schema & Premium Demo Data

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `activity_logs`;
DROP TABLE IF EXISTS `contact_messages`;
DROP TABLE IF EXISTS `reviews`;
DROP TABLE IF EXISTS `coupons`;
DROP TABLE IF EXISTS `order_items`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `product_images`;
DROP TABLE IF EXISTS `products`;
DROP TABLE IF EXISTS `categories`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `settings`;
SET FOREIGN_KEY_CHECKS = 1;

-- Users Table
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `role` ENUM('admin', 'customer') DEFAULT 'customer',
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories Table
CREATE TABLE `categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `description` TEXT DEFAULT NULL,
  `image_url` VARCHAR(255) DEFAULT NULL,
  `sort_order` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products Table
CREATE TABLE `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) NOT NULL UNIQUE,
  `sku` VARCHAR(100) NOT NULL UNIQUE,
  `description` TEXT DEFAULT NULL,
  `short_description` TEXT DEFAULT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `sale_price` DECIMAL(10,2) DEFAULT NULL,
  `discount` DECIMAL(5,2) DEFAULT NULL,
  `stock_status` ENUM('instock', 'outofstock') DEFAULT 'instock',
  `weight` VARCHAR(50) DEFAULT NULL,
  `quantity` INT DEFAULT 0,
  `category_id` INT DEFAULT NULL,
  `brand` VARCHAR(100) DEFAULT 'Office Dairy',
  `is_featured` TINYINT(1) DEFAULT 0,
  `is_best_seller` TINYINT(1) DEFAULT 0,
  `is_latest` TINYINT(1) DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product Images Table
CREATE TABLE `product_images` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `is_main` TINYINT(1) DEFAULT 0,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders Table
CREATE TABLE `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_number` VARCHAR(100) NOT NULL UNIQUE,
  `user_id` INT DEFAULT NULL,
  `guest_name` VARCHAR(255) DEFAULT NULL,
  `guest_email` VARCHAR(255) DEFAULT NULL,
  `guest_phone` VARCHAR(50) DEFAULT NULL,
  `shipping_address` TEXT NOT NULL,
  `city` VARCHAR(100) NOT NULL,
  `postal_code` VARCHAR(50) NOT NULL,
  `payment_method` VARCHAR(50) DEFAULT 'cod',
  `payment_status` ENUM('pending', 'paid', 'refunded', 'failed') DEFAULT 'pending',
  `subtotal` DECIMAL(10,2) NOT NULL,
  `shipping_charge` DECIMAL(10,2) DEFAULT 0.00,
  `discount_amount` DECIMAL(10,2) DEFAULT 0.00,
  `coupon_code` VARCHAR(100) DEFAULT NULL,
  `total` DECIMAL(10,2) NOT NULL,
  `status` ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Items Table
CREATE TABLE `order_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `product_id` INT DEFAULT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `quantity` INT NOT NULL,
  `total` DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Coupons Table
CREATE TABLE `coupons` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(100) NOT NULL UNIQUE,
  `discount_type` ENUM('percentage', 'fixed') DEFAULT 'percentage',
  `discount_value` DECIMAL(10,2) NOT NULL,
  `min_spend` DECIMAL(10,2) DEFAULT 0.00,
  `expiry_date` DATE NOT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reviews Table
CREATE TABLE `reviews` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `product_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `rating` INT NOT NULL CHECK (`rating` BETWEEN 1 AND 5),
  `comment` TEXT NOT NULL,
  `status` ENUM('pending', 'approved', 'spam') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contact Messages Table
CREATE TABLE `contact_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity Logs Table
CREATE TABLE `activity_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `action` VARCHAR(255) NOT NULL,
  `details` TEXT DEFAULT NULL,
  `ip_address` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings Table
CREATE TABLE `settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY,
  `setting_value` TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'Office Dairy'),
('site_title', 'Premium 3D Custom Milk & Dairy Products Store'),
('site_description', '100% pure, farm-fresh, glass-packaged dairy drinks, ghee, malai, and ice cream.'),
('whatsapp_number', '919876543210'),
('shipping_charge', '50.00'),
('currency', 'INR'),
('currency_symbol', '₹'),
('theme_mode', 'light');

-- Seed Default Admin (Password is 'admin123' hashed with BCRYPT)
INSERT INTO `users` (`name`, `email`, `password`, `phone`, `role`, `status`) VALUES
('Office Dairy Admin', 'admin@officedairy.com', '$2y$12$Z0H1zIq6bQ08WvP2qZ6KmeN0X9q9p17x9G7D2Y1F5f1z3O4r6p8y.', '+91 98765 43210', 'admin', 'active');

-- Seed Categories
INSERT INTO `categories` (`id`, `name`, `slug`, `description`, `image_url`, `sort_order`, `status`) VALUES
(1, 'Fresh Milk', 'fresh-milk', 'Pure, pasteurized farm fresh cow and buffalo milk.', '/upload/categories/fresh-milk.jpg', 1, 'active'),
(2, 'Ghee', 'ghee', 'Aromatic hand-churned clarified butter.', '/upload/categories/ghee.jpg', 2, 'active'),
(3, 'Malai', 'malai', 'Rich, thick clotted fresh cream.', '/upload/categories/malai.jpg', 3, 'active'),
(4, 'Badam Milk Bottle', 'badam-milk-bottle', 'Chilled almond and saffron flavored rich milk.', '/upload/categories/badam-milk.jpg', 4, 'active'),
(5, 'Ice Cream', 'ice-cream', 'Artisanal rich churned ice creams.', '/upload/categories/ice-cream.jpg', 5, 'active');

-- Seed Products
INSERT INTO `products` (`id`, `name`, `slug`, `sku`, `description`, `short_description`, `price`, `sale_price`, `stock_status`, `weight`, `quantity`, `category_id`, `brand`, `is_featured`, `is_best_seller`, `is_latest`, `status`) VALUES
(1, 'Premium Cow Milk (Glass Bottle)', 'premium-cow-milk', 'OD-MILK-COW', '<p>Pure, grass-fed organic A2 cow milk packed with protein, vitamins, and natural minerals. Standardized, pasteurized, and delivered cold in eco-friendly glass bottles within 4 hours of milking.</p>', '100% Pure, grass-fed, glass-packaged organic cow milk.', 80.00, 75.00, 'instock', '1 Litre', 100, 1, 'Office Dairy', 1, 1, 1, 'active'),
(2, 'Pure Buffalo Milk (Glass Bottle)', 'pure-buffalo-milk', 'OD-MILK-BUF', '<p>High cream, rich, thick buffalo milk ideal for home-made paneer, curd, and tea. Processed with zero adulterants.</p>', 'Thick, high-fat organic buffalo milk.', 90.00, 85.00, 'instock', '1 Litre', 150, 1, 'Office Dairy', 1, 0, 1, 'active'),
(3, 'Aromatic Cow Ghee', 'aromatic-cow-ghee', 'OD-GHEE-COW', '<p>Traditional Bilona method hand-churned yellow cow ghee. Aromatic, granular texture with healthy fats that boost immunity.</p>', 'Traditional hand-churned aromatic cow ghee.', 750.00, 699.00, 'instock', '500 ml', 50, 2, 'Office Dairy', 1, 1, 0, 'active'),
(4, 'Thick Fresh Malai', 'thick-fresh-malai', 'OD-MALAI-THICK', '<p>Creamy, thick layer clotted malai prepared daily. Perfect for desserts or adding rich textures to premium recipes.</p>', 'Rich thick fresh clotted milk cream.', 120.00, 110.00, 'instock', '250 gm', 40, 3, 'Office Dairy', 1, 0, 1, 'active'),
(5, 'Premium Badam Saffron Milk', 'premium-badam-saffron-milk', 'OD-BADAM-SAFF', '<p>Delicious ready-to-drink badam milk infused with pure Kashmiri saffron, real almond flakes, and cardamom. Served chilled.</p>', 'Chilled healthy badam saffron milk drink.', 60.00, 50.00, 'instock', '200 ml', 200, 4, 'Office Dairy', 1, 1, 1, 'active'),
(6, 'Madagascar Vanilla Ice Cream', 'madagascar-vanilla-ice-cream', 'OD-IC-VANILLA', '<p>Super rich vanilla ice cream churned with organic fresh milk, cream, and organic Madagascar vanilla pods.</p>', 'Classic creamy rich vanilla ice cream.', 150.00, 130.00, 'instock', '500 ml', 80, 5, 'Office Dairy', 0, 1, 0, 'active');

-- Seed Product Images
INSERT INTO `product_images` (`product_id`, `image_url`, `is_main`) VALUES
(1, 'https://images.unsplash.com/photo-1563636619-e9143da7973b?auto=format&fit=crop&w=600&q=80', 1),
(2, 'https://images.unsplash.com/photo-1550583724-b2692b85b150?auto=format&fit=crop&w=600&q=80', 1),
(3, 'https://images.unsplash.com/photo-1589985270826-4b7bb135bc9d?auto=format&fit=crop&w=600&q=80', 1),
(4, 'https://images.unsplash.com/photo-1528750955906-79c22288c896?auto=format&fit=crop&w=600&q=80', 1),
(5, 'https://images.unsplash.com/photo-1571151680383-0946b10de16b?auto=format&fit=crop&w=600&q=80', 1),
(6, 'https://images.unsplash.com/photo-1572490122747-3968b75cc699?auto=format&fit=crop&w=600&q=80', 1);

-- Seed Coupons
INSERT INTO `coupons` (`code`, `discount_type`, `discount_value`, `min_spend`, `expiry_date`, `status`) VALUES
('PUREDAIRY10', 'percentage', 10.00, 200.00, '2027-12-31', 'active'),
('FRESH50', 'fixed', 50.00, 500.00, '2027-12-31', 'active');