app/schemas/e_commerce.sql

-- Sales Table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT,
    sale_amount REAL NOT NULL,
    sale_date DATE
);

-- Orders Table
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER DEFAULT 1,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES sales(id)
);


INSERT INTO sales (id, product_name, category, sale_amount, sale_date) VALUES
(1, 'Laptop', 'Electronics', 1200.0, '2023-01-01'),
(2, 'Phone', 'Electronics', 800.0, '2023-01-02'),
(3, 'Laptop', 'Electronics', 1500.0, '2023-01-03'),
(4, 'Tablet', 'Electronics', 600.0, '2023-01-04'),
(5, 'Phone', 'Electronics', 900.0, '2023-01-05'),
(6, 'Monitor', 'Accessories', 400.0, '2023-01-06'),
(7, 'Keyboard', 'Accessories', 100.0, '2023-01-07'),
(8, 'Mouse', 'Accessories', 50.0, '2023-01-08'),
(9, 'Charger', 'Accessories', 70.0, '2023-01-09'),
(10, 'Headphones', 'Accessories', 200.0, '2023-01-10');


INSERT INTO orders (order_id, customer_id, product_id, quantity, order_date) VALUES
(1, 2001, 1, 1, '2023-01-01'),
(2, 2002, 2, 2, '2023-01-02'),
(3, 2001, 3, 1, '2023-01-03'),
(4, 2003, 4, 1, '2023-01-04'),
(5, 2001, 5, 1, '2023-01-05'),
(6, 2004, 6, 1, '2023-01-06'),
(7, 2002, 7, 3, '2023-01-07'),
(8, 2003, 8, 2, '2023-01-08'),
(9, 2005, 9, 1, '2023-01-09'),
(10, 2004, 10, 1, '2023-01-10');