-- =====================================================================
-- Section 2b+: capture engine, signatures, CCTV scaffold, scheduled reports
-- Run once in phpMyAdmin against wbcorp_webapp.
-- =====================================================================

-- Operator signature, captured once at user creation or added later.
ALTER TABLE tblUser ADD COLUMN signature_path VARCHAR(255) NULL AFTER full_name;

-- Per-transaction captured driver signature (distinct from the master's
-- default - a driver can sign fresh each time even if a default exists).
ALTER TABLE tblTransaction_Job ADD COLUMN driver_signature_path VARCHAR(255) NULL AFTER remarks;

-- CCTV: up to 4 RTSP cameras per site, configured by ISVAdmin. Actual
-- frame capture happens via a local agent extension (same pattern as the
-- weight agent) - this is the settings + storage side only.
CREATE TABLE tblCamera_Setting (
  camera_id      INT AUTO_INCREMENT PRIMARY KEY,
  site_id        INT NOT NULL,
  camera_name    VARCHAR(100) NOT NULL,
  rtsp_url       VARCHAR(255) NOT NULL,
  position_label VARCHAR(50),   -- e.g. 'Front', 'Rear', 'Left', 'Right'
  status         ENUM('Active','Inactive') DEFAULT 'Active',
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (site_id) REFERENCES tblSite(site_id)
) ENGINE=InnoDB;

CREATE TABLE tblTransaction_Photo (
  photo_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
  job_id      BIGINT NOT NULL,
  camera_id   INT NULL,
  photo_path  VARCHAR(255) NOT NULL,
  captured_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES tblTransaction_Job(job_id),
  FOREIGN KEY (camera_id) REFERENCES tblCamera_Setting(camera_id)
) ENGINE=InnoDB;

-- Scheduled email reports
CREATE TABLE tblReport_Schedule (
  schedule_id      INT AUTO_INCREMENT PRIMARY KEY,
  company_id       INT NOT NULL,
  frequency        ENUM('Daily','Weekly','Monthly') NOT NULL,
  recipient_emails TEXT NOT NULL,   -- comma-separated
  report_type      VARCHAR(50) DEFAULT 'Transaction Summary',
  last_run_at      DATETIME NULL,
  status           ENUM('Active','Inactive') DEFAULT 'Active',
  created_by       INT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES tblCompany(company_id)
) ENGINE=InnoDB;