-- Pending Admin Actions Table for Dual Authorization System
-- This table stores actions that require two admin signatures before execution

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'PENDING_ADMIN_ACTIONS_TBL')
BEGIN
    CREATE TABLE [VOLCANO_DBF].dbo.PENDING_ADMIN_ACTIONS_TBL (
        id INT IDENTITY(1,1) PRIMARY KEY,
        action_type VARCHAR(50) NOT NULL,              -- e.g., 'disable_coupon', 'delete_item', 'send_item', 'modify_balance'
        action_data NVARCHAR(MAX) NOT NULL,             -- JSON data containing action details
        requested_by VARCHAR(50) NOT NULL,              -- Account name of admin who initiated
        requested_at DATETIME NOT NULL DEFAULT GETDATE(),
        approved_by VARCHAR(50) NULL,                   -- Account name of admin who approved
        approved_at DATETIME NULL,
        rejected_by VARCHAR(50) NULL,                   -- Account name of admin who rejected
        rejected_at DATETIME NULL,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',  -- 'pending', 'approved', 'rejected', 'executed'
        executed_at DATETIME NULL,
        expiry_at DATETIME NULL,                        -- Optional expiry for time-sensitive actions
        notes NVARCHAR(500) NULL,                       -- Optional notes from requester
        rejection_reason NVARCHAR(500) NULL,            -- Reason for rejection
        approved_totp_code VARCHAR(6) NULL,             -- TOTP code used for approval (audit trail)
        approved_totp_verified_at DATETIME NULL,        -- When TOTP was verified for approval
        rejected_totp_code VARCHAR(6) NULL,             -- TOTP code used for rejection (audit trail)
        rejected_totp_verified_at DATETIME NULL,        -- When TOTP was verified for rejection
        INDEX idx_status (status),
        INDEX idx_requested_by (requested_by),
        INDEX idx_action_type (action_type),
        INDEX idx_created (requested_at DESC)
    );
END
GO
