database.sql

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'client') NOT NULL
);
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
    -- Add other fields as needed (e.g., description, stock, etc.)
);
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

    -- Sample data for users
    INSERT INTO users (username, password, role) VALUES
        ('admin', '$2y$10$exampleadminhash', 'admin'),
        ('client1', '$2y$10$exampleclienthash', 'client');

    -- Sample data for products
    INSERT INTO products (name, price) VALUES
        ('Paracetamol', 2.50),
        ('Ibuprofen', 3.00),
        ('Cough Syrup', 5.75),
        ('Vitamin C', 4.20);

    -- Sample data for orders
    INSERT INTO orders (user_id, order_date, total, status) VALUES
        (2, '2025-09-20 10:00:00', 8.50, 'completed'),
        (2, '2025-09-19 15:30:00', 3.00, 'pending');

    -- Sample data for order_items
    INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
        (1, 1, 2, 2.50), -- 2x Paracetamol
        (1, 4, 1, 4.20), -- 1x Vitamin C
        (2, 2, 1, 3.00); -- 1x Ibuprofen

    -- Sample data for inventory
    INSERT INTO inventory (product_id, quantity) VALUES
        (1, 100),
        (2, 80),
        (3, 50),
        (4, 120);