-- RetailPOS Kenya — Full Schema
-- Supports: packet→unit decomposition, bundle pricing, multi-shop, multi-tenant

CREATE DATABASE IF NOT EXISTS pos_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pos_system;

CREATE TABLE IF NOT EXISTS owners (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(150) NOT NULL,
  email      VARCHAR(150) UNIQUE NOT NULL,
  password   VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shops (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  owner_id   INT NOT NULL,
  name       VARCHAR(150) NOT NULL,
  location   VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  shop_id    INT NOT NULL,
  owner_id   INT NOT NULL,
  username   VARCHAR(100) NOT NULL,
  password   VARCHAR(255) NOT NULL,
  role       ENUM('owner','cashier') DEFAULT 'cashier',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS categories (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  shop_id INT NOT NULL,
  name    VARCHAR(100) NOT NULL,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
);

-- Products: supports packet-based buying + unit-level selling
-- cost_price     = cost per purchase packet/unit from supplier (e.g. KES 50 per packet)
-- stock_unit_size= how many base units per purchase packet (e.g. 6 cakes per packet)
--                  stock is always stored in base (smallest sellable) units
-- base_unit_qty  = legacy/alias for stock_unit_size, kept for compatibility
CREATE TABLE IF NOT EXISTS products (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  shop_id             INT NOT NULL,
  category_id         INT,
  name                VARCHAR(150) NOT NULL,
  cost_price          DECIMAL(10,2) DEFAULT 0,   -- cost per purchase packet
  stock_unit_size     INT DEFAULT 1,              -- base units per packet (e.g. 6)
  base_unit_qty       INT DEFAULT 1,              -- alias; same meaning as stock_unit_size
  low_stock_threshold INT DEFAULT 5,              -- alert when stock (base units) < this
  is_bundle           BOOLEAN DEFAULT 0,
  created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Selling units — each row is ONE way to sell this product
-- quantity      = how many BASE units this selling unit contains
--                 e.g. "Piece" → quantity=1  (1 cake)
--                      "3 for 10" bundle → quantity=3
-- selling_price = price for this unit/bundle
CREATE TABLE IF NOT EXISTS product_units (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  product_id    INT NOT NULL,
  unit_name     VARCHAR(50) NOT NULL,   -- e.g. "Piece", "3 for KES 10", "Pack of 6"
  quantity      INT NOT NULL DEFAULT 1, -- base units in this selling unit
  selling_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Inventory always stores BASE (smallest sellable) unit count
CREATE TABLE IF NOT EXISTS inventory (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL UNIQUE,
  quantity   INT DEFAULT 0,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Full audit trail; change_qty in BASE units (negative = sale/deduction)
CREATE TABLE IF NOT EXISTS stock_transactions (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  product_id   INT NOT NULL,
  change_qty   INT NOT NULL,
  type         ENUM('purchase','sale','adjustment') NOT NULL,
  notes        VARCHAR(255),
  reference_id INT,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS sales (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  shop_id        INT NOT NULL,
  user_id        INT NOT NULL,
  total_amount   DECIMAL(10,2) NOT NULL,
  amount_paid    DECIMAL(10,2) NOT NULL,
  change_given   DECIMAL(10,2) NOT NULL,
  payment_method ENUM('cash','mpesa','bank') NOT NULL,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shop_id) REFERENCES shops(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- price = price per selling unit at time of sale (snapshot)
-- quantity = number of selling units sold
-- subtotal = price * quantity
-- stock deducted = product_units.quantity * sale_items.quantity  (base units)
CREATE TABLE IF NOT EXISTS sale_items (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  sale_id         INT NOT NULL,
  product_id      INT NOT NULL,
  product_unit_id INT NOT NULL,
  quantity        INT NOT NULL,
  price           DECIMAL(10,2) NOT NULL,
  subtotal        DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (sale_id)         REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (product_unit_id) REFERENCES product_units(id)
);