-- Pacific Logistics · slice 05 · Extend rsflow customers table for Pacific cargo shape
--
-- Rsflow's customers table is broadly usable as-is: it already carries TIN, VRN,
-- POC (point of contact) fields, addresses, sector, and tier. Pacific customers
-- (mostly companies, not individuals) need a few extra billing fields on top,
-- and we relabel `fname` as "Company name" in the UI. Column names stay put
-- so any rsflow code that reads them continues to work.

-- MySQL 8 does NOT support `ADD COLUMN IF NOT EXISTS` (that's MariaDB). Since
-- these columns don't yet exist on a fresh install of Pacific, a plain ALTER
-- works. Re-running this slice on an already-migrated DB will error on the
-- duplicates — that's fine (it's a one-shot slice).

ALTER TABLE customers
  ADD COLUMN tenant_id            INT UNSIGNED  NOT NULL DEFAULT 1    AFTER c0d,
  ADD COLUMN credit_limit         DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER status,
  ADD COLUMN payment_terms_days   INT           NOT NULL DEFAULT 0    AFTER credit_limit,
  ADD COLUMN currency             VARCHAR(6)    NOT NULL DEFAULT 'TZS' AFTER payment_terms_days,
  ADD COLUMN opening_balance      DECIMAL(14,2) NOT NULL DEFAULT 0.00 AFTER currency,
  ADD COLUMN account_manager_id   INT UNSIGNED  NULL                  AFTER opening_balance,
  ADD COLUMN billing_email        VARCHAR(120)  NOT NULL DEFAULT ''   AFTER account_manager_id,
  ADD COLUMN notes                TEXT          NULL                  AFTER billing_email,
  ADD KEY idx_customers_status (status, deleted),
  ADD KEY idx_customers_tenant (tenant_id);


-- Grant Customers perms to Pacific roles.
-- Admin (10) already has grants from rsflow's original slices; add Pacific-specific roles.
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Administrator (10) — full (idempotent grant, safe if already present)
(10, 'Customers', 'index',  1, NOW()),
(10, 'Customers', 'add',    1, NOW()),
(10, 'Customers', 'save',   1, NOW()),
(10, 'Customers', 'view',   1, NOW()),
(10, 'Customers', 'edit',   1, NOW()),
(10, 'Customers', 'update', 1, NOW()),
(10, 'Customers', 'delete', 1, NOW()),
-- Executive (1) — read
(1,  'Customers', 'index',  1, NOW()),
(1,  'Customers', 'view',   1, NOW()),
-- Ops Manager (20) — read + edit (owns customer relationship for cargo)
(20, 'Customers', 'index',  1, NOW()),
(20, 'Customers', 'add',    1, NOW()),
(20, 'Customers', 'save',   1, NOW()),
(20, 'Customers', 'view',   1, NOW()),
(20, 'Customers', 'edit',   1, NOW()),
(20, 'Customers', 'update', 1, NOW()),
-- Dispatcher (21) — read (needs customer context on trip requests)
(21, 'Customers', 'index',  1, NOW()),
(21, 'Customers', 'view',   1, NOW()),
-- Accountant (3) — full (owns AR)
(3,  'Customers', 'index',  1, NOW()),
(3,  'Customers', 'add',    1, NOW()),
(3,  'Customers', 'save',   1, NOW()),
(3,  'Customers', 'view',   1, NOW()),
(3,  'Customers', 'edit',   1, NOW()),
(3,  'Customers', 'update', 1, NOW()),
(3,  'Customers', 'delete', 1, NOW()),
-- Finance Manager (2) — full
(2,  'Customers', 'index',  1, NOW()),
(2,  'Customers', 'add',    1, NOW()),
(2,  'Customers', 'save',   1, NOW()),
(2,  'Customers', 'view',   1, NOW()),
(2,  'Customers', 'edit',   1, NOW()),
(2,  'Customers', 'update', 1, NOW()),
(2,  'Customers', 'delete', 1, NOW());
