-- Pacific Logistics · slice 41 · Mobile app install-link flow
--
-- Admin generates a shareable URL that a driver hits from their phone
-- browser; the driver fills in name + phone before the APK downloads.
-- Both tables track full provenance so admins can see who installed
-- from which link and revoke a link if it leaks.

CREATE TABLE IF NOT EXISTS app_install_tokens (
    c0d              INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id        INT NOT NULL DEFAULT 1,
    token            VARCHAR(48)  NOT NULL UNIQUE,
    label            VARCHAR(120) NOT NULL DEFAULT '' COMMENT 'Friendly label admin sets (e.g. "New drivers cohort 3")',
    created_by       INT NOT NULL,
    created_by_name  VARCHAR(120) NOT NULL DEFAULT '',
    created_on       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_on       DATETIME NOT NULL,
    revoked          TINYINT(1) NOT NULL DEFAULT 0,
    revoked_by       INT NULL,
    revoked_on       DATETIME NULL,
    downloads_count  INT NOT NULL DEFAULT 0,
    last_download_on DATETIME NULL,
    KEY idx_ait_created (created_by, created_on),
    KEY idx_ait_active  (revoked, expires_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_install_downloads (
    c0d              INT AUTO_INCREMENT PRIMARY KEY,
    token_id         INT NOT NULL,
    downloader_name        VARCHAR(160) NOT NULL,
    downloader_phone       VARCHAR(40)  NOT NULL,
    downloader_employee_no VARCHAR(40)  NULL,
    ip_address       VARCHAR(45) NULL,
    user_agent       VARCHAR(255) NULL,
    downloaded_on    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_aid_token (token_id, downloaded_on),
    KEY idx_aid_phone (downloader_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Grants — Administrator + HR Officer can generate/manage tokens
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'Download', 'index',    1, NOW()),
(10, 'Download', 'generate', 1, NOW()),
(10, 'Download', 'revoke',   1, NOW()),
(10, 'Download', 'view',     1, NOW()),
(10, 'Download', 'apkAdmin', 1, NOW()),
(24, 'Download', 'index',    1, NOW()),
(24, 'Download', 'generate', 1, NOW()),
(24, 'Download', 'revoke',   1, NOW()),
(24, 'Download', 'view',     1, NOW()),
(24, 'Download', 'apkAdmin', 1, NOW());
-- Install controller is public — no grants; router allows it.

SELECT 'slice 41 imported' AS status,
       (SELECT COUNT(*) FROM app_install_tokens)    AS tokens,
       (SELECT COUNT(*) FROM app_install_downloads) AS downloads;
