-- Run once in phpMyAdmin (after migration 17).
-- Phase 2: sharing between sites of ONE company.
--   * a "sharing group" = sites that share open jobs and/or completed-transaction reports (a site is in at most one group)
--   * every job remembers the sites it was weighed at, so a truck weighed at Site A and again at Site B shows up for both
-- Nothing changes until you create a group under Administration -> Site Sharing.

CREATE TABLE tblSite_Group (
  group_id        INT AUTO_INCREMENT PRIMARY KEY,
  company_id      INT NOT NULL,
  group_name      VARCHAR(100) NOT NULL,
  share_open_jobs TINYINT(1) NOT NULL DEFAULT 1,     -- an open job started at one site can be continued at the others
  share_reports   TINYINT(1) NOT NULL DEFAULT 1,     -- the sites see each other's transactions in Reports
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_site_group_name (company_id, group_name),
  CONSTRAINT fk_site_group_company FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ON DELETE CASCADE
) ENGINE=InnoDB;

ALTER TABLE tblSite ADD COLUMN group_id INT NULL;
ALTER TABLE tblSite ADD CONSTRAINT fk_site_group FOREIGN KEY (group_id) REFERENCES tblSite_Group(group_id) ON DELETE SET NULL;

CREATE TABLE tblTransaction_Job_Site (
  job_id   BIGINT NOT NULL,
  site_id  INT NOT NULL,
  first_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (job_id, site_id),
  KEY idx_job_site_site (site_id),
  CONSTRAINT fk_job_site_job  FOREIGN KEY (job_id)  REFERENCES tblTransaction_Job(job_id) ON DELETE CASCADE,
  CONSTRAINT fk_job_site_site FOREIGN KEY (site_id) REFERENCES tblSite(site_id)
) ENGINE=InnoDB;

-- existing transactions: the site they started at, and every site a weighing was taken at
INSERT IGNORE INTO tblTransaction_Job_Site (job_id, site_id) SELECT job_id, site_id FROM tblTransaction_Job;
INSERT IGNORE INTO tblTransaction_Job_Site (job_id, site_id)
  SELECT DISTINCT w.job_id, wb.site_id FROM tblTransaction_Weighing w JOIN tblWeighbridge wb ON wb.device_id = w.device_id WHERE w.device_id IS NOT NULL;