-- =====================================================================
-- Section 1: Masters - run once in phpMyAdmin against wbcorp_webapp
-- Extends Customer/Supplier with payment terms, adds Driver/Vehicle(extends
-- Truck)/Transporter/Item(extends Product) masters, stock ledger, and an
-- optional photo on audit log entries.
-- =====================================================================

ALTER TABLE tblCustomer_Master
  ADD COLUMN payment_terms ENUM('Cash','Credit') DEFAULT 'Cash' AFTER contact_no,
  ADD COLUMN credit_limit DOUBLE NULL AFTER payment_terms,
  ADD COLUMN remarks VARCHAR(255) NULL AFTER credit_limit;

ALTER TABLE tblSupplier_Master
  ADD COLUMN payment_terms ENUM('Cash','Credit') DEFAULT 'Cash' AFTER contact_no,
  ADD COLUMN credit_limit DOUBLE NULL AFTER payment_terms,
  ADD COLUMN remarks VARCHAR(255) NULL AFTER credit_limit;

-- Vehicle Master = tblTruck_Master extended. tare_weight 0 or NULL means
-- "no known tare" - the transaction engine (Section 2) then behaves like a
-- normal first+second weighing instead of pulling tare from this record.
ALTER TABLE tblTruck_Master
  ADD COLUMN tare_weight DOUBLE NULL DEFAULT NULL AFTER driver_name,
  ADD COLUMN transporter_id INT NULL AFTER tare_weight,
  ADD COLUMN driver_id INT NULL AFTER transporter_id,
  ADD COLUMN remarks VARCHAR(255) NULL AFTER driver_id;

-- Item Master = tblProduct_Master extended with pricing, running stock, and
-- a DEFAULT deduction rule (operator can still key a manual value per
-- transaction in Section 2 - this is just the master-level default).
ALTER TABLE tblProduct_Master
  ADD COLUMN purchase_price DOUBLE DEFAULT 0 AFTER uom,
  ADD COLUMN sales_price DOUBLE DEFAULT 0 AFTER purchase_price,
  ADD COLUMN current_stock DOUBLE DEFAULT 0 AFTER sales_price,
  ADD COLUMN default_deduction_type ENUM('None','Percent','Fixed') DEFAULT 'None' AFTER current_stock,
  ADD COLUMN default_deduction_value DOUBLE DEFAULT 0 AFTER default_deduction_type,
  ADD COLUMN remarks VARCHAR(255) NULL AFTER default_deduction_value;

CREATE TABLE tblDriver_Master (
  driver_id      INT AUTO_INCREMENT PRIMARY KEY,
  company_id     INT NOT NULL,
  driver_name    VARCHAR(100) NOT NULL,
  license_no     VARCHAR(50),
  contact_no     VARCHAR(30),
  signature_path VARCHAR(255) NULL,
  remarks        VARCHAR(255),
  status         ENUM('Active','Inactive') DEFAULT 'Active',
  created_by     INT NULL, updated_by INT NULL,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NULL,
  FOREIGN KEY (company_id) REFERENCES tblCompany(company_id)
) ENGINE=InnoDB;

CREATE TABLE tblTransporter_Master (
  transporter_id   INT AUTO_INCREMENT PRIMARY KEY,
  company_id       INT NOT NULL,
  transporter_name VARCHAR(150) NOT NULL,
  contact_no       VARCHAR(30),
  remarks          VARCHAR(255),
  status           ENUM('Active','Inactive') DEFAULT 'Active',
  created_by       INT NULL, updated_by INT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NULL,
  FOREIGN KEY (company_id) REFERENCES tblCompany(company_id)
) ENGINE=InnoDB;

ALTER TABLE tblTruck_Master
  ADD CONSTRAINT fk_truck_transporter FOREIGN KEY (transporter_id) REFERENCES tblTransporter_Master(transporter_id),
  ADD CONSTRAINT fk_truck_driver FOREIGN KEY (driver_id) REFERENCES tblDriver_Master(driver_id);

-- Stock ledger: every IN/OUT movement, with a running balance for history/
-- audit. tblProduct_Master.current_stock is the fast-path cache for display.
CREATE TABLE tblStock_Ledger (
  ledger_id      BIGINT AUTO_INCREMENT PRIMARY KEY,
  company_id     INT NOT NULL,
  item_id        INT NOT NULL,
  movement_type  ENUM('In','Out') NOT NULL,
  quantity       DOUBLE NOT NULL,
  reference_type VARCHAR(50),   -- e.g. 'Transaction', 'Manual Adjustment', 'Opening Stock'
  reference_id   VARCHAR(50),
  balance_after  DOUBLE NOT NULL,
  remarks        VARCHAR(255),
  created_by     INT NULL,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES tblCompany(company_id),
  FOREIGN KEY (item_id) REFERENCES tblProduct_Master(product_id)
) ENGINE=InnoDB;

-- Audit trail: optional webcam photo attached to a sensitive action (manual
-- weight entry, override, edit, void, price change) when the operator
-- grants camera permission in the browser (Section 2/6).
ALTER TABLE tblAudit_Log ADD COLUMN photo_path VARCHAR(255) NULL AFTER detail;