-- ═══════════════════════════════════════════════════
-- QAFYS — 004 Audit & Security Schema
-- Audit logs, SSN access tracking, refresh tokens
-- ═══════════════════════════════════════════════════

-- ────────────────────────────────────────
-- General Audit Log
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`          INT UNSIGNED AUTO_INCREMENT,
  `actor_id`    VARCHAR(20)  DEFAULT NULL,    -- Who performed the action
  `actor_name`  VARCHAR(200) DEFAULT '',
  `action`      VARCHAR(50)  NOT NULL,        -- e.g., 'user.create', 'user.delete', 'ssn.view'
  `target_type` VARCHAR(50)  DEFAULT NULL,    -- e.g., 'user', 'attendance'
  `target_id`   VARCHAR(20)  DEFAULT NULL,
  `details`     JSON         DEFAULT NULL,    -- Additional context
  `ip_address`  VARCHAR(45)  DEFAULT NULL,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_al_actor` (`actor_id`),
  KEY `idx_al_action` (`action`),
  KEY `idx_al_target` (`target_type`, `target_id`),
  KEY `idx_al_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- SSN Access Log (dedicated for compliance)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ssn_access_logs` (
  `id`            INT UNSIGNED AUTO_INCREMENT,
  `viewer_id`     VARCHAR(20)  NOT NULL,
  `viewer_name`   VARCHAR(200) NOT NULL,
  `staff_id`      VARCHAR(20)  NOT NULL,
  `staff_name`    VARCHAR(200) NOT NULL,
  `reason`        TEXT         NOT NULL,
  `ip_address`    VARCHAR(45)  DEFAULT NULL,
  `created_at`    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ssn_viewer` (`viewer_id`),
  KEY `idx_ssn_staff` (`staff_id`),
  KEY `idx_ssn_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ────────────────────────────────────────
-- Refresh Tokens (for JWT rotation & revocation)
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `id`          INT UNSIGNED AUTO_INCREMENT,
  `user_id`     VARCHAR(20)  NOT NULL,
  `token_hash`  VARCHAR(255) NOT NULL,
  `expires_at`  TIMESTAMP    NOT NULL,
  `revoked`     TINYINT(1)   DEFAULT 0,
  `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rt_user` (`user_id`),
  KEY `idx_rt_token` (`token_hash`),
  KEY `idx_rt_expires` (`expires_at`),
  CONSTRAINT `fk_rt_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;