-- =============================================================================
-- SiloMart — ONE FILE SQL IMPORTER (MariaDB / MySQL / phpMyAdmin)
-- =============================================================================
-- How to import (cPanel, no SSH):
--   1. cPanel → MySQL Databases → create database + user + ALL PRIVILEGES
--   2. phpMyAdmin → select that database (left sidebar)
--   3. Import → Choose file → select this file → Go
--
-- This file:
--   - Creates all SiloMart tables + foreign keys
--   - Inserts default SiteSettings
--   - Inserts starter admin + customer users
--   - Inserts default policy/content pages
--
-- Grocery catalog on the live storefront is bundled in the app (static bins).
-- Extra products can still be added in Admin after import.
--
-- Default logins (CHANGE IMMEDIATELY on production — never shown on /login):
--   Admin:    admin@silomart.com / admin123
--   Customer: alex@example.com / password123
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `OrderItem`;
DROP TABLE IF EXISTS `Order`;
DROP TABLE IF EXISTS `Product`;
DROP TABLE IF EXISTS `Category`;
DROP TABLE IF EXISTS `StripeWebhookEvent`;
DROP TABLE IF EXISTS `RateLimitBucket`;
DROP TABLE IF EXISTS `ContentPage`;
DROP TABLE IF EXISTS `SiteSettings`;
DROP TABLE IF EXISTS `User`;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `User` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `passwordHash` VARCHAR(191) NOT NULL,
  `role` ENUM('customer', 'admin') NOT NULL DEFAULT 'customer',
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `User_email_key`(`email`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `Category` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `description` TEXT NOT NULL,
  `image` TEXT NOT NULL,
  `gender` VARCHAR(191) NOT NULL DEFAULT 'all',
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `Category_slug_key`(`slug`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `Product` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `price` DOUBLE NOT NULL,
  `compareAt` DOUBLE NULL,
  `categoryId` VARCHAR(191) NOT NULL,
  `categoryName` VARCHAR(191) NOT NULL,
  `categorySlug` VARCHAR(191) NOT NULL,
  `gender` ENUM('men', 'women', 'unisex') NOT NULL,
  `description` TEXT NOT NULL,
  `shortDescription` TEXT NOT NULL,
  `longDescription` TEXT NOT NULL,
  `details` JSON NOT NULL,
  `materials` JSON NOT NULL,
  `care` JSON NOT NULL,
  `fit` TEXT NOT NULL,
  `sizeGuide` JSON NOT NULL,
  `sizeGuideNote` TEXT NOT NULL,
  `images` JSON NOT NULL,
  `variants` JSON NOT NULL,
  `stock` INTEGER NOT NULL DEFAULT 0,
  `rating` DOUBLE NOT NULL DEFAULT 4.5,
  `reviewCount` INTEGER NOT NULL DEFAULT 0,
  `tags` JSON NOT NULL,
  `featured` BOOLEAN NOT NULL DEFAULT false,
  `flashSale` BOOLEAN NOT NULL DEFAULT false,
  `recentlyOrdered` BOOLEAN NOT NULL DEFAULT false,
  `bestSeller` BOOLEAN NOT NULL DEFAULT false,
  `newArrival` BOOLEAN NOT NULL DEFAULT false,
  `seller` VARCHAR(191) NOT NULL DEFAULT 'SiloMart',
  `verified` BOOLEAN NOT NULL DEFAULT true,
  `sold` INTEGER NULL,
  `flashLimit` INTEGER NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `Product_slug_key`(`slug`),
  INDEX `Product_categorySlug_idx`(`categorySlug`),
  INDEX `Product_gender_idx`(`gender`),
  INDEX `Product_featured_idx`(`featured`),
  INDEX `Product_flashSale_idx`(`flashSale`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `Order` (
  `id` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NULL,
  `customerName` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `phone` VARCHAR(191) NULL,
  `status` ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',
  `subtotal` DOUBLE NOT NULL,
  `shipping` DOUBLE NOT NULL,
  `discount` DOUBLE NOT NULL DEFAULT 0,
  `total` DOUBLE NOT NULL,
  `shippingAddress` TEXT NOT NULL,
  `paymentMethod` VARCHAR(191) NULL,
  `paymentId` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  INDEX `Order_userId_idx`(`userId`),
  INDEX `Order_email_idx`(`email`),
  INDEX `Order_status_idx`(`status`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `OrderItem` (
  `id` VARCHAR(191) NOT NULL,
  `orderId` VARCHAR(191) NOT NULL,
  `productId` VARCHAR(191) NULL,
  `name` VARCHAR(191) NOT NULL,
  `price` DOUBLE NOT NULL,
  `quantity` INTEGER NOT NULL,
  `image` TEXT NOT NULL,
  `variant` VARCHAR(191) NULL,
  INDEX `OrderItem_orderId_idx`(`orderId`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `SiteSettings` (
  `id` VARCHAR(191) NOT NULL DEFAULT 'default',
  `siteName` VARCHAR(191) NOT NULL DEFAULT 'SiloMart',
  `tagline` VARCHAR(191) NOT NULL DEFAULT 'Honest bins. Quiet prices.',
  `description` TEXT NOT NULL,
  `logoUrl` TEXT NOT NULL,
  `faviconUrl` TEXT NOT NULL,
  `supportEmail` VARCHAR(191) NOT NULL DEFAULT 'support@silomart.com',
  `supportPhone` VARCHAR(191) NOT NULL DEFAULT '+1 (800) 555-0147',
  `address` TEXT NOT NULL,
  `facebookUrl` TEXT NOT NULL,
  `instagramUrl` TEXT NOT NULL,
  `twitterUrl` TEXT NOT NULL,
  `youtubeUrl` TEXT NOT NULL,
  `tiktokUrl` TEXT NOT NULL,
  `currency` VARCHAR(191) NOT NULL DEFAULT 'USD',
  `footerTagline` TEXT NOT NULL,
  `footerNote` VARCHAR(191) NOT NULL DEFAULT 'Pickup · Delivery · Aisle walk',
  `stripeEnabled` BOOLEAN NOT NULL DEFAULT false,
  `stripePublishableKey` TEXT NOT NULL,
  `stripeSecretKey` TEXT NOT NULL,
  `smtpHost` VARCHAR(191) NOT NULL DEFAULT '',
  `smtpPort` INTEGER NOT NULL DEFAULT 587,
  `smtpUser` VARCHAR(191) NOT NULL DEFAULT '',
  `smtpPass` TEXT NOT NULL,
  `smtpFrom` VARCHAR(191) NOT NULL DEFAULT '',
  `smtpSecure` BOOLEAN NOT NULL DEFAULT false,
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `ContentPage` (
  `id` VARCHAR(191) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `title` VARCHAR(191) NOT NULL,
  `excerpt` TEXT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `published` BOOLEAN NOT NULL DEFAULT true,
  `showInFooter` BOOLEAN NOT NULL DEFAULT true,
  `footerGroup` VARCHAR(191) NOT NULL DEFAULT 'legal',
  `sortOrder` INTEGER NOT NULL DEFAULT 0,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE INDEX `ContentPage_slug_key`(`slug`),
  INDEX `ContentPage_published_showInFooter_idx`(`published`, `showInFooter`),
  INDEX `ContentPage_footerGroup_sortOrder_idx`(`footerGroup`, `sortOrder`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `RateLimitBucket` (
  `id` VARCHAR(191) NOT NULL,
  `count` INTEGER NOT NULL DEFAULT 0,
  `resetAt` DATETIME(3) NOT NULL,
  INDEX `RateLimitBucket_resetAt_idx`(`resetAt`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `StripeWebhookEvent` (
  `id` VARCHAR(255) NOT NULL,
  `type` VARCHAR(191) NOT NULL,
  `processedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `Product`
  ADD CONSTRAINT `Product_categoryId_fkey`
  FOREIGN KEY (`categoryId`) REFERENCES `Category`(`id`)
  ON DELETE RESTRICT ON UPDATE CASCADE;

ALTER TABLE `Order`
  ADD CONSTRAINT `Order_userId_fkey`
  FOREIGN KEY (`userId`) REFERENCES `User`(`id`)
  ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `OrderItem`
  ADD CONSTRAINT `OrderItem_orderId_fkey`
  FOREIGN KEY (`orderId`) REFERENCES `Order`(`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `OrderItem`
  ADD CONSTRAINT `OrderItem_productId_fkey`
  FOREIGN KEY (`productId`) REFERENCES `Product`(`id`)
  ON DELETE SET NULL ON UPDATE CASCADE;

INSERT INTO `User` (`id`, `name`, `email`, `passwordHash`, `role`, `createdAt`, `updatedAt`) VALUES
('admin_seed_001', 'SiloMart Admin', 'admin@silomart.com', '$2b$12$my8A/bI1XtyB0jNHRX4q3.dDYNMi/hUXJSrZxbV.UG0ldFRUCrCkS', 'admin', NOW(3), NOW(3)),
('customer_seed_001', 'Alex Morgan', 'alex@example.com', '$2b$12$ep1QuZmFDdl/.dw9SQ6gGePV7odiRZHBxQKV640Rf/pWMUJ9zV4RG', 'customer', NOW(3), NOW(3));

INSERT INTO `SiteSettings` (
  `id`, `siteName`, `tagline`, `description`, `logoUrl`, `faviconUrl`,
  `supportEmail`, `supportPhone`, `address`,
  `facebookUrl`, `instagramUrl`, `twitterUrl`, `youtubeUrl`, `tiktokUrl`,
  `currency`, `footerTagline`, `footerNote`,
  `stripeEnabled`, `stripePublishableKey`, `stripeSecretKey`,
  `smtpHost`, `smtpPort`, `smtpUser`, `smtpPass`, `smtpFrom`, `smtpSecure`, `updatedAt`
) VALUES (
  'default',
  'SiloMart',
  'Honest bins. Quiet prices.',
  'SiloMart is a neighborhood grocer: weekly crib circulars, limited Silo Drops, pickup, delivery, and aisle walk.',
  '',
  '/favicon.png',
  'support@silomart.com',
  '+1 (800) 555-0147',
  '1200 Market Street, Suite 400, San Francisco, CA 94103, USA',
  '',
  '',
  '',
  '',
  '',
  'USD',
  'A mill-direct neighborhood grocer — short bins, dated drops, quiet prices.',
  'Pickup · Delivery · Aisle walk',
  0,
  '',
  '',
  '',
  587,
  '',
  '',
  '',
  0,
  NOW(3)
);

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_about', 'about', 'About SiloMart', 'A mill-direct neighborhood grocer.', 'SiloMart is a neighborhood grocer built around the crib: a short set of bins, private-label mill brands, and a dated drop calendar. We do not run a coupon maze. The ticket on the tile is the ticket at the window.\n\nDelivery, curb pickup, and aisle walk share the same catalog — prices still follow the crib you pick. Silo Drops leave when the week turns. Bin Cuts are honest markdowns against last week’s compare-at.\n\nBring a tote. Bag your own.', 1, 1, 'company', 10, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_contact', 'contact', 'Contact us', 'Reach SiloMart support.', 'Need help with an order, a drop, or a pickup window?\n\nEmail: support@silomart.com\nHours: Monday–Friday, 9:00–18:00 (US Eastern)\n\nUse the contact form on this page for the fastest response. Include your order ID when writing about a purchase.', 1, 1, 'company', 20, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_privacy', 'privacy', 'Privacy policy', 'How we collect and use your information.', 'Last updated: August 2026\n\nSiloMart (“we”, “us”) respects your privacy. This policy explains what we collect and why.\n\nInformation we collect\n• Account details such as name and email when you register\n• Order and shipping details needed to fulfill purchases\n• Payment references processed by Stripe (we do not store full card numbers)\n• Basic site analytics and device information\n\nHow we use information\n• To process orders, returns, and customer support\n• To improve the storefront and prevent fraud\n• To send order updates and, if you opt in, promotional emails\n\nSharing\nWe share data only with service providers required to run the store (payments, email delivery, hosting). We do not sell personal information.\n\nYour choices\nYou may request access or deletion of your account data by emailing support@silomart.com.', 1, 1, 'legal', 10, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_terms', 'terms', 'Terms of service', 'Rules for using SiloMart.', 'Last updated: August 2026\n\nBy using SiloMart you agree to these terms.\n\nAccounts\nYou are responsible for keeping your login secure and for activity under your account.\n\nOrders & pricing\nPrices are shown in the selected store currency and may change without notice before checkout is completed. We may cancel or refuse an order in cases of pricing errors, stock issues, or suspected fraud.\n\nAcceptable use\nDo not misuse the site, attempt unauthorized access, or post unlawful content.\n\nLiability\nProducts are sold as described. To the fullest extent permitted by law, SiloMart is not liable for indirect or consequential damages.\n\nContact\nQuestions about these terms: support@silomart.com', 1, 1, 'legal', 20, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_refund_policy', 'refund-policy', 'Refund policy', 'When and how refunds are issued.', 'Last updated: August 2026\n\nEligibility\nMost unused items in original condition may be refunded within 30 days of delivery. Final-sale or perishable items may be excluded and will be labeled on the product page.\n\nProcess\n1. Contact support@silomart.com with your order ID\n2. Receive return instructions\n3. Ship the item back using a trackable method\n4. After inspection, refunds are issued to the original payment method\n\nTiming\nRefunds typically appear within 5–10 business days after approval, depending on your bank or card issuer.\n\nDamaged or incorrect items\nIf something arrives damaged or wrong, contact us within 7 days with photos — we will prioritize a refund or replacement.', 1, 1, 'support', 10, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_return_policy', 'return-policy', 'Return policy', 'How to return or exchange an item.', 'Last updated: August 2026\n\nReturn window\nYou may start a return within 30 days of delivery for eligible products.\n\nCondition\nItems should be unused, in original packaging, with tags attached where applicable.\n\nExchanges\nIf you need a different variant (size/pack), contact support first. Exchanges depend on stock availability.\n\nShipping costs\nReturn shipping is the customer’s responsibility unless the item is defective or we made an error. In those cases we cover return shipping.\n\nHow to start\nEmail support@silomart.com with your order ID and reason for return. We will reply with the return address and next steps.', 1, 1, 'support', 20, NOW(3), NOW(3));

INSERT INTO `ContentPage` (`id`, `slug`, `title`, `excerpt`, `content`, `published`, `showInFooter`, `footerGroup`, `sortOrder`, `createdAt`, `updatedAt`) VALUES
('page_shipping', 'shipping', 'Shipping policy', 'Delivery timelines and pickup options.', 'Last updated: August 2026\n\nProcessing\nOrders usually ship or stage for pickup within 1–3 business days after payment confirmation.\n\nDelivery estimates\n• United States: 3–7 business days after shipment\n• Pickup: ready during your chosen window after we confirm the order\n\nTracking\nTracking details are emailed when your order ships. You can also review status under Account → Orders.\n\nCustoms & duties\nInternational customers are responsible for any import duties or taxes charged by their country.', 1, 1, 'support', 30, NOW(3), NOW(3));

SELECT 'SiloMart import complete' AS status;