-- ============================================================
-- Schema Update v7
-- Run this AFTER schema_update_v6.sql.
-- Adds a dedicated Waste & Breakage module - separate from supplier returns
-- and generic adjustments, with a manageable list of reasons (Breakage,
-- Spillage, Expired, Spoilage, Preparation Waste, Theft/Loss, Other...) and
-- its own report. Works for both Main Store and individual Outlets, and
-- supports multiple item lines per waste record (like PR/SR do).
-- ============================================================

ALTER TABLE stock_transactions
  MODIFY txn_type ENUM('grn_in','issue_out','transfer_in','transfer_out','adjustment_in','adjustment_out','return_out','waste_out') NOT NULL;

CREATE TABLE IF NOT EXISTS waste_reasons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT IGNORE INTO waste_reasons (name) VALUES
 ('Breakage'), ('Spillage'), ('Expired'), ('Spoilage/Went Bad'),
 ('Preparation Waste'), ('Damaged in Storage'), ('Theft/Unaccounted Loss'), ('Other');

CREATE TABLE IF NOT EXISTS waste_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  waste_number VARCHAR(30) UNIQUE NOT NULL,
  location_type ENUM('main_store','outlet') NOT NULL,
  outlet_id INT NULL, -- set when location_type = 'outlet', else NULL for main store
  recorded_by INT NOT NULL,
  total_value DECIMAL(14,2) DEFAULT 0,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE SET NULL,
  FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS waste_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  waste_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) DEFAULT 0,
  reason_id INT NOT NULL,
  notes VARCHAR(255) NULL,
  FOREIGN KEY (waste_id) REFERENCES waste_records(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id),
  FOREIGN KEY (reason_id) REFERENCES waste_reasons(id)
) ENGINE=InnoDB;

-- Give the admin role access to the new module by default (everyone else
-- starts at none, same as every other permission - turn on via Role
-- Permissions if managers/supervisors should be able to record waste too)
INSERT IGNORE INTO role_permissions (role, permission_code) VALUES ('admin', 'waste.manage');
