-- TPIN (Transaction PIN) module
-- Safe to re-run: uses information_schema checks / INSERT ... WHERE NOT EXISTS
-- Users set a personal PIN in profile; admins assign who must use it.
-- Wired to: report exports, payroll sensitive actions, approval decide actions.

-- ---------------------------------------------------------------------------
-- users: TPIN columns
-- ---------------------------------------------------------------------------
SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'tpin_hash'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `users` ADD COLUMN `tpin_hash` VARCHAR(255) NULL AFTER `remember_token`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'tpin_set_at'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `users` ADD COLUMN `tpin_set_at` DATETIME NULL AFTER `tpin_hash`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'tpin_required'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `users` ADD COLUMN `tpin_required` TINYINT(1) NOT NULL DEFAULT 0 AFTER `tpin_set_at`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'tpin_failed_count'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `users` ADD COLUMN `tpin_failed_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `tpin_required`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'tpin_locked_until'
);
SET @sql := IF(
    @col_exists = 0,
    'ALTER TABLE `users` ADD COLUMN `tpin_locked_until` DATETIME NULL AFTER `tpin_failed_count`',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- Permissions
-- ---------------------------------------------------------------------------
INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'tpin', 'tpin.assign', 'Assign TPIN Requirement', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'tpin.assign');

INSERT INTO `permissions` (`group`, `slug`, `label`, `is_system`, `created_at`)
SELECT 'tpin', 'tpin.clear', 'Clear User TPIN', 1, NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `permissions` WHERE `slug` = 'tpin.clear');

INSERT INTO `role_permissions` (`role_id`, `permission_id`, `created_at`)
SELECT r.id, p.id, NOW()
FROM `roles` r
CROSS JOIN `permissions` p
WHERE r.slug = 'administrator'
  AND p.slug IN ('tpin.assign', 'tpin.clear')
  AND NOT EXISTS (
      SELECT 1 FROM `role_permissions` rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
