-- =================================================================================
-- Software Requirements Specification (SRS) - Data Schema for RetailMind
-- Version: 2.0
-- Database: MySQL 8.0
--
-- This script provides a complete and formal definition of the database
-- schema for the RetailMind application. It includes table structures,
-- primary and foreign key constraints, and a full set of INSERT statements
-- to populate the database with the exact dummy data used throughout the application.
-- =================================================================================


-- =================================================================================
-- Table: products
-- Description: Stores details about each product available for sale.
-- =================================================================================
CREATE TABLE `products` (
  `id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100),
  `price` DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT='Master table for all products offered.';

-- =================================================================================
-- Table: outlets
-- Description: Stores details for each retail location, including physical stores and online presence.
-- =================================================================================
CREATE TABLE `outlets` (
  `id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `latitude` DECIMAL(9, 6),
  `longitude` DECIMAL(9, 6),
  PRIMARY KEY (`id`)
) COMMENT='Defines all retail locations, both physical and virtual.';

-- =================================================================================
-- Table: customers
-- Description: Stores customer profile information.
-- =================================================================================
CREATE TABLE `customers` (
  `id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) UNIQUE,
  `last_purchase_date` VARCHAR(50),
  `churn_probability` INT,
  PRIMARY KEY (`id`)
) COMMENT='Stores profiles for all customers.';

-- =================================================================================
-- Table: inventory
-- Description: A join table mapping products to outlets to store current stock levels.
-- This represents the inventory count of a specific product at a specific outlet.
-- =================================================================================
CREATE TABLE `inventory` (
  `product_id` VARCHAR(50) NOT NULL,
  `outlet_id` VARCHAR(50) NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`product_id`, `outlet_id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`),
  FOREIGN KEY (`outlet_id`) REFERENCES `outlets`(`id`)
) COMMENT='Tracks stock levels for each product at each physical outlet.';

-- =================================================================================
-- Table: sales
-- Description: A transactional log representing the complete purchase history.
-- It links products, customers, and outlets for each transaction.
-- =================================================================================
CREATE TABLE `sales` (
  `id` VARCHAR(50) NOT NULL,
  `product_id` VARCHAR(50) NOT NULL,
  `customer_id` VARCHAR(50) NOT NULL,
  `outlet_id` VARCHAR(50) NOT NULL,
  `quantity_sold` INT NOT NULL,
  `revenue` DECIMAL(10, 2) NOT NULL,
  `sale_date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`),
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
  FOREIGN KEY (`outlet_id`) REFERENCES `outlets`(`id`)
) COMMENT='Transactional log of all sales, linking products, customers, and outlets.';


-- =================================================================================
-- Data Population (INSERT Statements)
-- =================================================================================

-- ---------------------------------------------------------------------------------
-- Populate `products` table
-- ---------------------------------------------------------------------------------
INSERT INTO `products` (`id`, `name`, `category`, `price`) VALUES
('PROD-001', 'Classic Leather Wallet', 'Accessories', 39.99),
('PROD-002', 'Noise-Cancelling Headphones', 'Electronics', 199.99),
('PROD-003', 'Smart Home Hub', 'Electronics', 129.00),
('PROD-004', 'Organic Cotton T-Shirt', 'Apparel', 25.00),
('PROD-005', 'Gourmet Coffee Beans', 'Groceries', 18.50),
('PROD-006', 'Stainless Steel Water Bottle', 'Accessories', 22.00),
('PROD-007', 'Portable Bluetooth Speaker', 'Electronics', 45.00),
('PROD-008', 'Men\'s Denim Jeans', 'Apparel', 75.00),
('PROD-009', 'Artisanal Dark Chocolate', 'Groceries', 8.99),
('PROD-010', 'Yoga Mat', 'Sports', 35.00);

-- ---------------------------------------------------------------------------------
-- Populate `outlets` table
-- ---------------------------------------------------------------------------------
INSERT INTO `outlets` (`id`, `name`, `latitude`, `longitude`) VALUES
('OUTLET-001', 'Downtown Flagship', 34.052200, -118.243700),
('OUTLET-002', 'Mall Kiosk', 34.062200, -118.253700),
('OUTLET-003', 'Online Store', 0.000000, 0.000000),
('OUTLET-004', 'Suburban Branch', 34.152200, -118.343700),
('OUTLET-005', 'Airport Kiosk', 33.941600, -118.408500),
('OUTLET-006', 'Westside Outlet', 34.052200, -118.443700),
('OUTLET-007', 'Eastside Outlet', 34.052200, -118.143700),
('OUTLET-008', 'Uptown Boutique', 34.082200, -118.293700),
('OUTLET-009', 'Warehouse Direct', 33.952200, -118.243700),
('OUTLET-010', 'Financial District Pop-up', 34.048800, -118.251800);

-- ---------------------------------------------------------------------------------
-- Populate `customers` table
-- ---------------------------------------------------------------------------------
INSERT INTO `customers` (`id`, `name`, `email`, `last_purchase_date`, `churn_probability`) VALUES
('CUST-001', 'Jane Doe', 'jane.d@example.com', '3 months ago', 85),
('CUST-002', 'John Smith', 'j.smith@example.com', '1 month ago', 60),
('CUST-003', 'Alice Johnson', 'alice.j@example.com', '2 weeks ago', 25),
('CUST-004', 'Robert Brown', 'rob.b@example.com', '5 months ago', 92),
('CUST-005', 'Emily White', 'emily.w@example.com', '1 week ago', 10),
('CUST-006', 'Michael Green', 'michael.g@example.com', '2 months ago', 70),
('CUST-007', 'Sarah Black', 'sarah.b@example.com', '6 months ago', 95),
('CUST-008', 'David King', 'david.k@example.com', '3 weeks ago', 30),
('CUST-009', 'Laura Hill', 'laura.h@example.com', '1 day ago', 5),
('CUST-010', 'James Clark', 'james.c@example.com', '4 months ago', 88);

-- ---------------------------------------------------------------------------------
-- Populate `sales` table (Purchase History)
-- This data is derived from `granularSalesData` in the application.
-- ---------------------------------------------------------------------------------
INSERT INTO `sales` (`id`, `product_id`, `customer_id`, `outlet_id`, `quantity_sold`, `revenue`, `sale_date`) VALUES
('SALE-001', 'PROD-002', 'CUST-001', 'OUTLET-001', 5, 999.95, '2024-01-15 14:30:00'),
('SALE-002', 'PROD-004', 'CUST-002', 'OUTLET-002', 25, 625.00, '2024-01-18 12:00:00'),
('SALE-003', 'PROD-003', 'CUST-003', 'OUTLET-003', 12, 1548.00, '2024-02-01 10:00:00'),
('SALE-004', 'PROD-001', 'CUST-004', 'OUTLET-001', 8, 319.92, '2024-02-05 11:45:00'),
('SALE-005', 'PROD-002', 'CUST-005', 'OUTLET-003', 7, 1399.93, '2024-02-10 09:30:00'),
('SALE-006', 'PROD-005', 'CUST-006', 'OUTLET-002', 30, 555.00, '2024-02-20 16:00:00'),
('SALE-007', 'PROD-004', 'CUST-007', 'OUTLET-001', 12, 300.00, '2024-03-01 13:15:00'),
('SALE-008', 'PROD-003', 'CUST-008', 'OUTLET-004', 6, 774.00, '2024-03-02 18:00:00'),
('SALE-009', 'PROD-001', 'CUST-009', 'OUTLET-003', 15, 599.85, '2024-03-10 20:00:00'),
('SALE-010', 'PROD-005', 'CUST-010', 'OUTLET-004', 20, 370.00, '2024-03-12 14:00:00'),
('SALE-011', 'PROD-002', 'CUST-001', 'OUTLET-004', 3, 599.97, '2024-04-05 17:30:00'),
('SALE-012', 'PROD-004', 'CUST-002', 'OUTLET-003', 40, 1000.00, '2024-04-08 11:00:00'),
('SALE-013', 'PROD-006', 'CUST-003', 'OUTLET-005', 15, 330.00, '2024-04-15 08:00:00'),
('SALE-014', 'PROD-007', 'CUST-004', 'OUTLET-006', 4, 180.00, '2024-04-22 19:00:00'),
('SALE-015', 'PROD-008', 'CUST-005', 'OUTLET-007', 9, 675.00, '2024-05-01 15:00:00'),
('SALE-016', 'PROD-009', 'CUST-006', 'OUTLET-008', 20, 179.80, '2024-05-05 13:30:00'),
('SALE-017', 'PROD-010', 'CUST-007', 'OUTLET-009', 3, 105.00, '2024-05-10 09:00:00'),
('SALE-018', 'PROD-003', 'CUST-008', 'OUTLET-005', 2, 258.00, '2024-05-15 18:45:00'),
('SALE-019', 'PROD-004', 'CUST-009', 'OUTLET-006', 18, 450.00, '2024-05-20 12:30:00'),
('SALE-020', 'PROD-001', 'CUST-010', 'OUTLET-010', 6, 239.94, '2024-06-01 12:00:00'),
('SALE-021', 'PROD-005', 'CUST-001', 'OUTLET-008', 12, 222.00, '2024-06-05 16:30:00'),
('SALE-022', 'PROD-002', 'CUST-002', 'OUTLET-007', 2, 399.98, '2024-06-10 14:00:00'),
('SALE-023', 'PROD-006', 'CUST-003', 'OUTLET-003', 30, 660.00, '2024-06-15 11:00:00'),
('SALE-024', 'PROD-007', 'CUST-004', 'OUTLET-002', 10, 450.00, '2024-06-20 19:30:00'),
('SALE-025', 'PROD-008', 'CUST-005', 'OUTLET-004', 5, 375.00, '2024-06-25 17:00:00');

-- ---------------------------------------------------------------------------------
-- Populate `inventory` table
-- This data is simulated to reflect varied stock levels across outlets,
-- mirroring the logic in the application's inventory page.
-- ---------------------------------------------------------------------------------
INSERT INTO `inventory` (`product_id`, `outlet_id`, `quantity`) VALUES
-- PROD-001 at different outlets
('PROD-001', 'OUTLET-001', 12), ('PROD-001', 'OUTLET-002', 180), ('PROD-001', 'OUTLET-004', 215), ('PROD-001', 'OUTLET-005', 145), ('PROD-001', 'OUTLET-006', 255), ('PROD-001', 'OUTLET-007', 290), ('PROD-001', 'OUTLET-008', 110), ('PROD-001', 'OUTLET-009', 240), ('PROD-001', 'OUTLET-010', 160),
-- PROD-002 at different outlets
('PROD-002', 'OUTLET-001', 11), ('PROD-002', 'OUTLET-002', 20), ('PROD-002', 'OUTLET-004', 555), ('PROD-002', 'OUTLET-005', 405), ('PROD-002', 'OUTLET-006', 705), ('PROD-002', 'OUTLET-007', 810), ('PROD-002', 'OUTLET-008', 300), ('PROD-002', 'OUTLET-009', 660), ('PROD-002', 'OUTLET-010', 435),
-- PROD-003 at different outlets
('PROD-003', 'OUTLET-001', 250), ('PROD-003', 'OUTLET-002', 290), ('PROD-003', 'OUTLET-004', 365), ('PROD-003', 'OUTLET-005', 265), ('PROD-003', 'OUTLET-006', 465), ('PROD-003', 'OUTLET-007', 530), ('PROD-003', 'OUTLET-008', 200), ('PROD-003', 'OUTLET-009', 440), ('PROD-003', 'OUTLET-010', 290),
-- PROD-004 at different outlets
('PROD-004', 'OUTLET-001', 770), ('PROD-004', 'OUTLET-002', 40), ('PROD-004', 'OUTLET-004', 44), ('PROD-004', 'OUTLET-005', 540), ('PROD-004', 'OUTLET-006', 940), ('PROD-004', 'OUTLET-007', 1080), ('PROD-004', 'OUTLET-008', 400), ('PROD-004', 'OUTLET-009', 880), ('PROD-004', 'OUTLET-010', 580),
-- PROD-005 at different outlets
('PROD-005', 'OUTLET-001', 375), ('PROD-005', 'OUTLET-002', 435), ('PROD-005', 'OUTLET-004', 547), ('PROD-005', 'OUTLET-005', 397), ('PROD-005', 'OUTLET-006', 697), ('PROD-005', 'OUTLET-007', 795), ('PROD-005', 'OUTLET-008', 300), ('PROD-005', 'OUTLET-009', 660), ('PROD-005', 'OUTLET-010', 435),
-- PROD-006 at different outlets
('PROD-006', 'OUTLET-001', 390), ('PROD-006', 'OUTLET-002', 450), ('PROD-006', 'OUTLET-004', 36), ('PROD-006', 'OUTLET-005', 420), ('PROD-006', 'OUTLET-006', 720), ('PROD-006', 'OUTLET-007', 822), ('PROD-006', 'OUTLET-008', 312), ('PROD-006', 'OUTLET-009', 684), ('PROD-006', 'OUTLET-010', 450),
-- PROD-007 at different outlets
('PROD-007', 'OUTLET-001', 6), ('PROD-007', 'OUTLET-002', 260), ('PROD-007', 'OUTLET-004', 320), ('PROD-007', 'OUTLET-005', 240), ('PROD-007', 'OUTLET-006', 400), ('PROD-007', 'OUTLET-007', 464), ('PROD-007', 'OUTLET-008', 168), ('PROD-007', 'OUTLET-009', 376), ('PROD-007', 'OUTLET-010', 248),
-- PROD-008 at different outlets
('PROD-008', 'OUTLET-001', 5), ('PROD-008', 'OUTLET-002', 227), ('PROD-008', 'OUTLET-004', 288), ('PROD-008', 'OUTLET-005', 213), ('PROD-008', 'OUTLET-006', 351), ('PROD-008', 'OUTLET-007', 406), ('PROD-008', 'OUTLET-008', 147), ('PROD-008', 'OUTLET-009', 329), ('PROD-008', 'OUTLET-010', 217),
-- PROD-009 at different outlets
('PROD-009', 'OUTLET-001', 625), ('PROD-009', 'OUTLET-002', 725), ('PROD-009', 'OUTLET-004', 900), ('PROD-009', 'OUTLET-005', 650), ('PROD-009', 'OUTLET-006', 1100), ('PROD-009', 'OUTLET-007', 1250), ('PROD-009', 'OUTLET-008', 500), ('PROD-009', 'OUTLET-009', 1100), ('PROD-009', 'OUTLET-010', 725),
-- PROD-010 at different outlets
('PROD-010', 'OUTLET-001', 300), ('PROD-010', 'OUTLET-002', 348), ('PROD-010', 'OUTLET-004', 444), ('PROD-010', 'OUTLET-005', 324), ('PROD-010', 'OUTLET-006', 528), ('PROD-010', 'OUTLET-007', 612), ('PROD-010', 'OUTLET-008', 228), ('PROD-010', 'OUTLET-009', 504), ('PROD-010', 'OUTLET-010', 336);
