-- =====================================================================
-- Device Binding — schema
--
-- Locks one Login ID to one physical device. First login after the
-- feature is enabled binds the user to the device they log in from;
-- every later login must come from that same device until an admin
-- releases the binding. A release is one-shot: the next login on any
-- device re-binds and locks again.
--
-- Run once per database (live / staging / local).
-- All statements are idempotent-safe to re-run except the ALTER, which
-- is guarded below.
-- =====================================================================


-- ---------------------------------------------------------------------
-- 1. Per-tenant switch.
--    Device binding is OFF for every tenant until explicitly enabled,
--    so turning this on for one company cannot affect the others that
--    share this backend.
--
--    crm_app_license_master is returned wholesale (SELECT *) to the app
--    at login, so this column reaches the client with no API change.
-- ---------------------------------------------------------------------
-- Guarded ALTER: skips cleanly if the column already exists.
SET @ddl := (
  SELECT IF(
    COUNT(*) = 0,
    'ALTER TABLE `crm_app_license_master`
       ADD COLUMN `DeviceBindingEnabled` VARCHAR(5) NOT NULL DEFAULT ''No''',
    'SELECT ''column DeviceBindingEnabled already present'''
  )
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'crm_app_license_master'
    AND COLUMN_NAME  = 'DeviceBindingEnabled'
);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------
-- 2. The binding itself — exactly one row per (AppCode, UserId).
--    Status 'active'   = locked to DeviceId
--           'released' = admin unlocked; next login re-binds
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_device_binding` (
  `BindingId`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `AppCode`            VARCHAR(50)  NOT NULL,
  `UserId`             VARCHAR(50)  NOT NULL,
  `DeviceId`           VARCHAR(191) NOT NULL,
  `DeviceModel`        VARCHAR(191)     DEFAULT NULL,
  `DeviceManufacturer` VARCHAR(191)     DEFAULT NULL,
  `DevicePlatform`     VARCHAR(50)      DEFAULT NULL,
  `DeviceOSVersion`    VARCHAR(50)      DEFAULT NULL,
  `AppVersion`         VARCHAR(50)      DEFAULT NULL,
  `Status`             VARCHAR(20)  NOT NULL DEFAULT 'active',
  `BoundAt`            DATETIME         DEFAULT NULL,
  `LastSeenAt`         DATETIME         DEFAULT NULL,
  `ReleasedAt`         DATETIME         DEFAULT NULL,
  `ReleasedBy`         VARCHAR(50)      DEFAULT NULL,
  `ReleaseReason`      VARCHAR(500)     DEFAULT NULL,
  `CreatedAt`          DATETIME         DEFAULT NULL,
  `UpdatedAt`          DATETIME         DEFAULT NULL,
  PRIMARY KEY (`BindingId`),
  UNIQUE KEY `uq_user_device_binding_user`  (`AppCode`, `UserId`),
  KEY          `idx_user_device_binding_dev` (`AppCode`, `DeviceId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ---------------------------------------------------------------------
-- 3. Unlock requests raised by a blocked employee from the app.
--    At most one 'pending' row per user is enforced in application code
--    (a rejected request must be re-raisable, so no unique key here).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_device_request` (
  `RequestId`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `AppCode`              VARCHAR(50)  NOT NULL,
  `UserId`               VARCHAR(50)  NOT NULL,
  `RequestedDeviceId`    VARCHAR(191) NOT NULL,
  `RequestedDeviceModel` VARCHAR(191)     DEFAULT NULL,
  `RequestedDevicePlatform` VARCHAR(50)   DEFAULT NULL,
  `RequestedDeviceOSVersion` VARCHAR(50)  DEFAULT NULL,
  `CurrentDeviceId`      VARCHAR(191)     DEFAULT NULL,
  `Reason`               VARCHAR(500)     DEFAULT NULL,
  `Status`               VARCHAR(20)  NOT NULL DEFAULT 'pending',
  `RequestedAt`          DATETIME         DEFAULT NULL,
  `ActionedAt`           DATETIME         DEFAULT NULL,
  `ActionedBy`           VARCHAR(50)      DEFAULT NULL,
  `ActionRemark`         VARCHAR(500)     DEFAULT NULL,
  `IpAddress`            VARCHAR(64)      DEFAULT NULL,
  `AppVersion`           VARCHAR(50)      DEFAULT NULL,
  PRIMARY KEY (`RequestId`),
  KEY `idx_user_device_request_queue` (`AppCode`, `Status`, `RequestedAt`),
  KEY `idx_user_device_request_user`  (`AppCode`, `UserId`, `Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ---------------------------------------------------------------------
-- 4. Append-only audit trail.
--    This is the evidence log for the malpractice problem: every bind,
--    every match, and — most usefully — every blocked attempt, with the
--    device that was refused.
--
--    Action: bound | matched | blocked | no_device_id | released
--          | request_raised | request_approved | request_rejected
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_device_log` (
  `LogId`      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `AppCode`    VARCHAR(50)  NOT NULL,
  `UserId`     VARCHAR(50)  NOT NULL,
  `DeviceId`   VARCHAR(191)     DEFAULT NULL,
  `Action`     VARCHAR(30)  NOT NULL,
  `Detail`     VARCHAR(500)     DEFAULT NULL,
  `IpAddress`  VARCHAR(64)      DEFAULT NULL,
  `AppVersion` VARCHAR(50)      DEFAULT NULL,
  `ActionBy`   VARCHAR(50)      DEFAULT NULL,
  `CreatedAt`  DATETIME         DEFAULT NULL,
  PRIMARY KEY (`LogId`),
  KEY `idx_user_device_log_user`   (`AppCode`, `UserId`, `CreatedAt`),
  KEY `idx_user_device_log_action` (`AppCode`, `Action`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ---------------------------------------------------------------------
-- 5. Enable for one tenant when you are ready to go live.
--    Replace <APPCODE> with the company's ApplicationCode.
--
--    UPDATE `crm_app_license_master`
--       SET `DeviceBindingEnabled` = 'Yes'
--     WHERE `ApplicationCode` = '<APPCODE>';
--
--    To roll back instantly, set it to 'No'. Bindings are preserved and
--    take effect again the moment it is switched back on.
-- ---------------------------------------------------------------------