-- ============================================================
-- Hotel Inventory & Requisition Management System
-- Database: bestwest_ivm
-- Run this in phpMyAdmin > bestwest_ivm > SQL tab
-- Assumes `users` and `loan_approval_stages` tables ALREADY EXIST.
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------
-- Departments (referenced by users.department_id)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT IGNORE INTO departments (id, name) VALUES
 (1,'Front Office'),(2,'Housekeeping'),(3,'Food & Beverage'),
 (4,'Kitchen'),(5,'Engineering'),(6,'Accounts'),(7,'Administration'),(8,'Stores');

-- ---------------------------------------------------------
-- Outlets (points of consumption: bars, restaurants, kitchen, housekeeping store etc.)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS outlets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  department_id INT NULL,
  location VARCHAR(150) NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Units of measure
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  abbreviation VARCHAR(10) NOT NULL
) ENGINE=InnoDB;

INSERT IGNORE INTO units (id, name, abbreviation) VALUES
 (1,'Pieces','pcs'),(2,'Kilogram','kg'),(3,'Litre','ltr'),(4,'Carton','ctn'),
 (5,'Pack','pack'),(6,'Bottle','btl'),(7,'Roll','roll'),(8,'Box','box');

-- ---------------------------------------------------------
-- Item categories
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT IGNORE INTO categories (id, name) VALUES
 (1,'Food Items'),(2,'Beverages'),(3,'Cleaning & Housekeeping'),
 (4,'Guest Amenities'),(5,'Stationery & Office'),(6,'Engineering & Maintenance'),(7,'Kitchen Supplies');

-- ---------------------------------------------------------
-- Suppliers
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  contact_person VARCHAR(100),
  phone VARCHAR(30),
  email VARCHAR(100),
  address VARCHAR(255),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Items (inventory catalogue)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) UNIQUE,
  name VARCHAR(150) NOT NULL,
  category_id INT NULL,
  unit_id INT NULL,
  reorder_level DECIMAL(12,2) DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Main store stock (current on-hand quantity + moving average cost)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS main_store_stock (
  item_id INT PRIMARY KEY,
  quantity DECIMAL(14,2) NOT NULL DEFAULT 0,
  avg_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Outlet stock (current on-hand quantity per outlet)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS outlet_stock (
  outlet_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (outlet_id, item_id),
  FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Stock transactions (full audit trail / ledger)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS stock_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  location_type ENUM('main_store','outlet') NOT NULL,
  location_id INT NULL, -- outlet id if location_type = outlet, else NULL for main store
  txn_type ENUM('grn_in','issue_out','transfer_in','transfer_out','adjustment_in','adjustment_out') NOT NULL,
  quantity DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) DEFAULT 0,
  reference_type VARCHAR(40) NULL, -- 'GRN','SR','ADJUSTMENT'
  reference_id INT NULL,
  created_by INT NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Authorization / Approval workflow configuration
-- Defines the ordered chain of roles required to approve a module
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS approval_workflows (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module ENUM('purchase_requisition','store_requisition') NOT NULL,
  stage_order INT NOT NULL,
  role_required ENUM('superadmin','admin','hr','audit','gm','accounts','manager','staff','supervisor','opm') NOT NULL,
  min_amount DECIMAL(14,2) DEFAULT 0,
  max_amount DECIMAL(14,2) DEFAULT NULL, -- NULL = no upper bound
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

-- Default workflow: Purchase Requisition -> Supervisor -> Manager -> Accounts -> GM (any amount)
INSERT INTO approval_workflows (module, stage_order, role_required, min_amount, max_amount) VALUES
 ('purchase_requisition',1,'supervisor',0,NULL),
 ('purchase_requisition',2,'manager',0,NULL),
 ('purchase_requisition',3,'accounts',0,NULL),
 ('purchase_requisition',4,'gm',0,NULL),
 ('store_requisition',1,'supervisor',0,NULL),
 ('store_requisition',2,'manager',0,NULL);

-- ---------------------------------------------------------
-- Purchase Requisitions
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS purchase_requisitions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pr_number VARCHAR(30) UNIQUE NOT NULL,
  requested_by INT NOT NULL,
  department_id INT NULL,
  outlet_id INT NULL,
  status ENUM('pending','approved','rejected','ordered','received','cancelled') DEFAULT 'pending',
  current_stage INT DEFAULT 1,
  total_estimated_cost DECIMAL(14,2) DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_requisition_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pr_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL,
  estimated_unit_cost DECIMAL(14,2) DEFAULT 0,
  supplier_id INT NULL,
  notes VARCHAR(255) NULL,
  FOREIGN KEY (pr_id) REFERENCES purchase_requisitions(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Purchase Orders (raised from an approved PR)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS purchase_orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  po_number VARCHAR(30) UNIQUE NOT NULL,
  pr_id INT NOT NULL,
  supplier_id INT NOT NULL,
  status ENUM('open','partially_received','received','cancelled') DEFAULT 'open',
  total_cost DECIMAL(14,2) DEFAULT 0,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (pr_id) REFERENCES purchase_requisitions(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  po_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) NOT NULL,
  quantity_received DECIMAL(14,2) DEFAULT 0,
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Goods Received Notes (receiving into Main Store)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS goods_received_notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grn_number VARCHAR(30) UNIQUE NOT NULL,
  po_id INT NOT NULL,
  received_by INT NOT NULL,
  received_date DATE NOT NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS goods_received_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  grn_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity_received DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) NOT NULL,
  FOREIGN KEY (grn_id) REFERENCES goods_received_notes(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Store Requisitions (outlet requests stock from Main Store)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS store_requisitions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sr_number VARCHAR(30) UNIQUE NOT NULL,
  outlet_id INT NOT NULL,
  requested_by INT NOT NULL,
  status ENUM('pending','approved','rejected','issued','cancelled') DEFAULT 'pending',
  current_stage INT DEFAULT 1,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS store_requisition_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sr_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity_requested DECIMAL(14,2) NOT NULL,
  quantity_issued DECIMAL(14,2) DEFAULT 0,
  FOREIGN KEY (sr_id) REFERENCES store_requisitions(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Approval logs (generic, used by both PR and SR)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS approval_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module ENUM('purchase_requisition','store_requisition') NOT NULL,
  reference_id INT NOT NULL,
  stage INT NOT NULL,
  approver_id INT NOT NULL,
  action ENUM('approved','rejected') NOT NULL,
  comments VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
