01_schema.sql

USE ecommerce_db;
-- E-COMMERCE SALES INTELLIGENCE PLATFORM
-- Phase 1: Database Schema


-- Drop tables if they exist (safe to re-run)
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;


-- TABLE 1: CUSTOMERS
-- Stores one row per unique customer

CREATE TABLE customers (
    customer_id     INT PRIMARY KEY AUTO_INCREMENT,
    customer_name   VARCHAR(100) NOT NULL,
    email           VARCHAR(150) UNIQUE NOT NULL,
    city            VARCHAR(80),
    state           VARCHAR(50),
    gender          ENUM('M', 'F', 'Other'),
    age             INT,
    signup_date     DATE NOT NULL,          -- when they first registered
    is_active       TINYINT DEFAULT 1       -- 1 = active, 0 = churned
);


-- TABLE 2: PRODUCTS
-- Master list of all products sold

CREATE TABLE products (
    product_id      INT PRIMARY KEY AUTO_INCREMENT,
    product_name    VARCHAR(150) NOT NULL,
    category        VARCHAR(80) NOT NULL,   -- e.g. Electronics, Clothing, Home
    sub_category    VARCHAR(80),
    cost_price      DECIMAL(10,2) NOT NULL, -- what we paid the supplier
    selling_price   DECIMAL(10,2) NOT NULL, -- what we charge the customer
    stock_quantity  INT DEFAULT 0           -- current inventory level
);


-- TABLE 3: ORDERS
-- One row per order placed by a customer
CREATE TABLE orders (
    order_id        INT PRIMARY KEY AUTO_INCREMENT,
    customer_id     INT NOT NULL,
    order_date      DATE NOT NULL,
    delivery_date   DATE,                   -- NULL means not yet delivered
    status          ENUM('Completed','Returned','Cancelled','Pending') DEFAULT 'Pending',
    payment_method  VARCHAR(50),            -- UPI, Credit Card, COD, etc.
    discount_pct    DECIMAL(5,2) DEFAULT 0, -- discount % applied at order level
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);


-- TABLE 4: ORDER_ITEMS
-- One row per product within an order
-- (an order can have multiple products)

CREATE TABLE order_items (
    item_id         INT PRIMARY KEY AUTO_INCREMENT,
    order_id        INT NOT NULL,
    product_id      INT NOT NULL,
    quantity        INT NOT NULL,
    unit_price      DECIMAL(10,2) NOT NULL, -- price at time of purchase (may differ from current)
    returned        TINYINT DEFAULT 0,      -- 1 = this specific item was returned
    FOREIGN KEY (order_id)    REFERENCES orders(order_id),
    FOREIGN KEY (product_id)  REFERENCES products(product_id)
);


-- INDEXES: speed up common queries

CREATE INDEX idx_orders_customer   ON orders(customer_id);
CREATE INDEX idx_orders_date       ON orders(order_date);
CREATE INDEX idx_items_order       ON order_items(order_id);
CREATE INDEX idx_items_product     ON order_items(product_id);