CREATE TABLE IF NOT EXISTS matrial (
  m_id INT NOT NULL AUTO_INCREMENT,
  material_name VARCHAR(100) NOT NULL,
  serial_number VARCHAR(30) NOT NULL,
  s_code INT NOT NULL DEFAULT 0,
  m_unit VARCHAR(20) NOT NULL DEFAULT 'Nos',
  nature_cmp INT NOT NULL DEFAULT 0,
  section VARCHAR(100) NOT NULL DEFAULT '',
  material_rate VARCHAR(20) NOT NULL DEFAULT '',
  min_stock VARCHAR(30) NOT NULL DEFAULT '',
  sihl_page VARCHAR(30) NOT NULL DEFAULT '',
  div_stock_sihl VARCHAR(30) NOT NULL DEFAULT '',
  user_id INT NOT NULL DEFAULT 0,
  update_id INT NOT NULL DEFAULT 0,
  nwdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (m_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS stock_transactions (
  id INT NOT NULL AUTO_INCREMENT,
  material_id INT NOT NULL,
  quantity DECIMAL(12,2) NOT NULL,
  transaction_type ENUM('receive','issue','return') NOT NULL,
  from_holder_type ENUM('external','bso','department','contractor','br','em','consumed') NOT NULL,
  from_holder_id INT NOT NULL DEFAULT 0,
  to_holder_type ENUM('external','bso','department','contractor','br','em','consumed') NOT NULL,
  to_holder_id INT NOT NULL,
  reference_no VARCHAR(100) DEFAULT NULL,
  remarks TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_stock_transactions_material (material_id),
  KEY idx_stock_transactions_from_holder (from_holder_type, from_holder_id),
  KEY idx_stock_transactions_to_holder (to_holder_type, to_holder_id),
  KEY idx_stock_transactions_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
