-- =====================================================================
-- Section 5: custom roles + configurable ticket numbering.
-- Run once in phpMyAdmin against wbcorp_webapp.
-- =====================================================================

-- ---- Custom roles --------------------------------------------------
-- company_id NULL = a built-in role (ISVAdmin, CompanyAdmin, Supervisor, Operator).
-- A custom role belongs to ONE company, so two companies can each have their own "Clerk".
ALTER TABLE tblRole ADD COLUMN company_id INT NULL AFTER role_id;
-- The old rule "role_name unique across everything" must go (it would stop two companies
-- using the same role name). If this line says the index doesn't exist, run
--   SHOW INDEX FROM tblRole;   and drop whichever UNIQUE index covers only role_name.
ALTER TABLE tblRole DROP INDEX role_name;
ALTER TABLE tblRole ADD UNIQUE KEY uq_role_company_name (company_id, role_name);
ALTER TABLE tblRole ADD CONSTRAINT fk_role_company FOREIGN KEY (company_id) REFERENCES tblCompany(company_id) ON DELETE CASCADE;

-- ---- Ticket numbering ----------------------------------------------
-- One row per site (no row = the original format TKT-<site>-<YYYYMMDD>-<0001>).
CREATE TABLE tblTicket_Numbering (
  site_id     INT PRIMARY KEY,
  prefix      VARCHAR(10) NOT NULL DEFAULT '',
  suffix      VARCHAR(10) NOT NULL DEFAULT '',
  format      VARCHAR(40) NOT NULL DEFAULT '{SEQ}',   -- tokens: {YYYY} {YY} {MM} {DD} {SITEID} {SEQ}
  seq_digits  INT NOT NULL DEFAULT 5,                 -- zero-padding of {SEQ}
  start_no    INT NOT NULL DEFAULT 1,                 -- first number issued when a counter begins
  reset_cycle ENUM('Never','Yearly','Monthly','Daily') NOT NULL DEFAULT 'Monthly',
  updated_by  INT NULL,
  updated_at  DATETIME NULL,
  FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- The running counter, one row per site per period (e.g. per month when reset_cycle = Monthly).
CREATE TABLE tblTicket_Counter (
  site_id    INT NOT NULL,
  period_key VARCHAR(12) NOT NULL,
  last_no    INT NOT NULL DEFAULT 0,
  PRIMARY KEY (site_id, period_key),
  FOREIGN KEY (site_id) REFERENCES tblSite(site_id) ON DELETE CASCADE
) ENGINE=InnoDB;