-- Run this file in MySQL to set up your POS database
-- Command: mysql -u root -p < setup.sql

CREATE DATABASE IF NOT EXISTS pos_db;
USE pos_db;

-- Products table
CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT DEFAULT 0,
  category VARCHAR(50) DEFAULT 'General',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order items table
CREATE TABLE IF NOT EXISTS 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)
);

-- Sample products
INSERT INTO products (name, price, stock, category) VALUES
  ('Espresso',        2.50,  100, 'Drinks'),
  ('Cappuccino',      3.50,  100, 'Drinks'),
  ('Latte',           4.00,  100, 'Drinks'),
  ('Croissant',       2.00,   50, 'Food'),
  ('Blueberry Muffin',2.50,   50, 'Food'),
  ('Sandwich',        5.00,   30, 'Food'),
  ('Orange Juice',    3.00,   40, 'Drinks'),
  ('Bottled Water',   1.50,   80, 'Drinks');