CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  phone VARCHAR(20),
  role VARCHAR(20) DEFAULT 'retailer',
  retailer_id INT,
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE retailers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(100),
  location VARCHAR(100),
  owner INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  unit VARCHAR(20),
  price INT,
  retailer_id INT,
  stock INT,
  low_stock_threshold INT DEFAULT 10
);

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  retailer_id INT,
  product_id INT,
  quantity INT,
  sale_amount INT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stock_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  retailer_id INT,
  product_id INT,
  quantity_added INT,
  cost_price INT,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);