-- Drink Warehouse Management Database Schema
-- Run: mysql -u user -p databasename < warehouse_schema.sql

CREATE DATABASE IF NOT EXISTS drink_warehouse DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE drink_warehouse;

-- Users table (admins and staff)
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150),
  password_hash VARCHAR(255) NOT NULL, -- store bcrypt/argon2 hash
  role ENUM('admin','cashier','verifier','loader') NOT NULL DEFAULT 'cashier',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Products (drinks)
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(60) UNIQUE,
  name VARCHAR(200) NOT NULL,
  volume VARCHAR(50),
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Stock entries (incoming stock that only admin can add)
CREATE TABLE stock_entries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  batch_code VARCHAR(100),
  source VARCHAR(200),
  received_by INT, -- admin id who added
  received_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  note TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (received_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Stock levels (calculated/current balance) -- can be maintained by triggers or app logic
CREATE TABLE stock_levels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL UNIQUE,
  quantity INT NOT NULL DEFAULT 0,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Sales header (created by cashier - user1)
CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(100) NOT NULL UNIQUE,
  cashier_id INT NOT NULL, -- user1 who created sale
  verifier_id INT DEFAULT NULL, -- user2 who confirms
  loader_id INT DEFAULT NULL, -- user3 who verifies outgoing
  status ENUM('pending','verified','completed','cancelled') DEFAULT 'pending',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  total_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (cashier_id) REFERENCES users(id),
  FOREIGN KEY (verifier_id) REFERENCES users(id),
  FOREIGN KEY (loader_id) REFERENCES users(id)
);

-- Items per sale
CREATE TABLE sale_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Payments (support part payments)
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method ENUM('cash','card','transfer','other') DEFAULT 'cash',
  reference VARCHAR(200), -- card or transfer reference
  paid_by VARCHAR(200),
  paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  recorded_by INT, -- staff who recorded payment
  FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Stock movements for items leaving warehouse (outgoing)
CREATE TABLE stock_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  movement_type ENUM('out','in') NOT NULL,
  related_sale_id INT DEFAULT NULL,
  performed_by INT, -- loader/user who confirmed
  performed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  note TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (related_sale_id) REFERENCES sales(id) ON DELETE SET NULL,
  FOREIGN KEY (performed_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Empties tracking (bottles/cases returned)
CREATE TABLE empties (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  recorded_by INT,
  recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  note TEXT,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Simple audit log
CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  action VARCHAR(200),
  details TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Sample admin user (password: changeit - store hashes in real life)
-- Note: Replace '<HASH>' with a real bcrypt/argon2 hash before using in production.
INSERT INTO users (username, full_name, email, password_hash, role) VALUES
('admin', 'Warehouse Admin', 'admin@example.com', '<REPLACE_WITH_HASH>', 'admin');