-- ============================================================
-- Schema Update v2
-- Run this AFTER the original schema.sql, in phpMyAdmin > SQL tab.
-- Adds: user email + signature, notifications, return-to-supplier.
-- NOTE: If a column/table already exists you may see a harmless
-- "Duplicate column" or "table already exists" error for that one
-- statement only — just skip it and run the rest.
-- ============================================================

-- Add email + signature support to the existing users table
ALTER TABLE users ADD COLUMN email VARCHAR(150) NULL AFTER username;
ALTER TABLE users ADD COLUMN signature_path VARCHAR(255) NULL AFTER can_add_staff;

-- Allow 'return_out' as a stock transaction type (goods sent back to supplier)
ALTER TABLE stock_transactions
  MODIFY txn_type ENUM('grn_in','issue_out','transfer_in','transfer_out','adjustment_in','adjustment_out','return_out') NOT NULL;

-- ---------------------------------------------------------
-- In-app notifications (backs up email in case mail delivery fails)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  module VARCHAR(40) NULL,
  reference_id INT NULL,
  message VARCHAR(255) NOT NULL,
  link VARCHAR(255) NULL,
  is_read TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Return to Supplier (defective / wrong / excess goods sent back)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS return_to_supplier (
  id INT AUTO_INCREMENT PRIMARY KEY,
  return_number VARCHAR(30) UNIQUE NOT NULL,
  po_id INT NULL,
  supplier_id INT NOT NULL,
  status ENUM('pending','sent','resolved') DEFAULT 'pending',
  reason VARCHAR(255) NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS return_to_supplier_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  return_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(14,2) NOT NULL,
  unit_cost DECIMAL(14,2) DEFAULT 0,
  reason VARCHAR(255) NULL,
  FOREIGN KEY (return_id) REFERENCES return_to_supplier(id) ON DELETE CASCADE,
  FOREIGN KEY (item_id) REFERENCES items(id)
) ENGINE=InnoDB;
