-- =====================================================================
-- Section 2c: GST, real CCTV credentials, capture-request queue
-- Run once in phpMyAdmin against wbcorp_webapp.
-- =====================================================================

ALTER TABLE tblTransaction_Setting ADD COLUMN gst_percent DECIMAL(5,2) DEFAULT 9.00 AFTER allow_reprint;

-- Replace the single rtsp_url field with the actual pieces you'll key in per
-- camera (IP, username, password), plus an editable path so the exact
-- working RTSP endpoint for your camera model can be set/corrected without
-- a code change. Password is stored ENCRYPTED (reversible), not hashed like
-- login passwords - the local agent needs the real value to authenticate to
-- the camera, so one-way hashing isn't usable here.
ALTER TABLE tblCamera_Setting
  ADD COLUMN ip_address VARCHAR(100) NOT NULL AFTER camera_name,
  ADD COLUMN username VARCHAR(100) NULL AFTER ip_address,
  ADD COLUMN password_encrypted VARCHAR(255) NULL AFTER username,
  ADD COLUMN rtsp_path VARCHAR(255) DEFAULT '/stream1' AFTER password_encrypted,
  ADD COLUMN rtsp_port INT DEFAULT 554 AFTER rtsp_path;
-- rtsp_url column, if it exists from the earlier scaffold, is superseded by the above -
-- drop it only if your table actually has it (it will if you ran the Section 2b migration):
ALTER TABLE tblCamera_Setting DROP COLUMN rtsp_url;

-- Polling queue: when a job completes, one row is queued per site. The local
-- agent polls this (same philosophy as the weight polling fix - reliable,
-- no event-handler flakiness), captures a snapshot from each active camera
-- on that site, uploads it, then marks the row Done.
CREATE TABLE tblCamera_Capture_Request (
  request_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
  job_id       BIGINT NOT NULL,
  site_id      INT NOT NULL,
  status       ENUM('Pending','Processing','Done','Failed') DEFAULT 'Pending',
  fail_reason  VARCHAR(255) NULL,
  requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id),
  FOREIGN KEY (site_id) REFERENCES tblSite(site_id)
) ENGINE=InnoDB;