-- Pacific Logistics · slice 17 · Mobile API foundation
--
-- Iter 3 Turn 3 adds:
--   * mobile-login columns on drivers (PIN hash + toggle) — drivers auth from
--     the mobile app via phone + PIN, NOT via the users table. Keeps staff
--     dashboard auth completely separate from field auth.
--   * api_tokens — bearer tokens issued on login, verified on every API call.
--     Rolling TTL: last_used_at bumps on every touch, expires 30 days after
--     last activity. Revocation via revoked_at timestamp (non-null = dead).
--   * api_audit_log — 1 row per API request, keyed to token/driver.
--     Kept for 90 days by nightly housekeeping (Turn 2 cron/nightly).


-- ═══════════════════════════════════════════════════════════════════════
-- drivers: add mobile-auth columns
-- ═══════════════════════════════════════════════════════════════════════
-- MySQL 8: ADD COLUMN IF NOT EXISTS is not supported the same way as PG.
-- Guard with a stored-proc so slice is idempotent.
DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_col_if_missing $$
CREATE PROCEDURE pl_add_col_if_missing(IN tbl VARCHAR(64), IN col VARCHAR(64), IN spec VARCHAR(500))
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND COLUMN_NAME = col
    ) THEN
        SET @s = CONCAT('ALTER TABLE ', tbl, ' ADD COLUMN ', col, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

CALL pl_add_col_if_missing('drivers', 'mobile_password_hash', 'VARCHAR(255) NULL');
CALL pl_add_col_if_missing('drivers', 'mobile_login_enabled', 'TINYINT(1) NOT NULL DEFAULT 0');
CALL pl_add_col_if_missing('drivers', 'mobile_last_login_at', 'DATETIME NULL');

DROP PROCEDURE pl_add_col_if_missing;


-- ═══════════════════════════════════════════════════════════════════════
-- api_tokens — bearer tokens for the mobile app
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS api_tokens (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    driver_id        INT UNSIGNED NOT NULL,
    token_hash       CHAR(64)     NOT NULL,       -- SHA-256 of the token
    device_label     VARCHAR(120) NULL,           -- 'John's Samsung A14' etc.
    device_platform  VARCHAR(20)  NULL,           -- android | ios | web
    app_version      VARCHAR(30)  NULL,
    ip_address       VARCHAR(45)  NULL,
    user_agent       VARCHAR(255) NULL,
    last_used_at     DATETIME     NULL,
    expires_at       DATETIME     NOT NULL,
    revoked_at       DATETIME     NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_token_hash (token_hash),
    KEY idx_at_driver (driver_id, revoked_at),
    KEY idx_at_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- api_audit_log — 1 row per API request. Small, indexed by driver + time.
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS api_audit_log (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    driver_id        INT UNSIGNED NULL,
    token_id         INT UNSIGNED NULL,
    endpoint         VARCHAR(60)  NOT NULL,       -- action name, e.g. 'login', 'pushPosition'
    http_method      VARCHAR(10)  NOT NULL,
    http_status      SMALLINT UNSIGNED NOT NULL,
    ip_address       VARCHAR(45)  NULL,
    user_agent       VARCHAR(255) NULL,
    error_msg        VARCHAR(255) NULL,
    duration_ms      INT UNSIGNED NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    KEY idx_al_driver_ts (driver_id, created_on),
    KEY idx_al_endpoint  (endpoint, created_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Seed: enable mobile login on the first available driver with PIN 1234
-- (dev only — production must set a strong PIN per driver via the UI).
-- ═══════════════════════════════════════════════════════════════════════
UPDATE drivers
   SET mobile_password_hash = '$2y$10$Ml3vk4jIvKfB3wJPuSpaC.EWmqf1kMGuNVJ4XRc6UyXaHhIeZFuT2',   -- password_hash('1234', PASSWORD_BCRYPT)
       mobile_login_enabled = 1
 WHERE c0d = (SELECT c0d FROM (SELECT c0d FROM drivers WHERE deleted = 0 AND mobile_login_enabled = 0 ORDER BY c0d LIMIT 1) t)
   AND mobile_password_hash IS NULL;

-- Note: Api controller is public (bearer-auth in-action, not perm-gated) —
-- no user_groups_perms grants needed.
