-- Migration: Create manager_approvals table
-- This table logs all manager/supervisor approvals with their capabilities
-- Ensures capabilities from groups are saved with each approval event

CREATE TABLE IF NOT EXISTS manager_approvals (
    approval_id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    auth_id VARCHAR(36) NOT NULL,
    capabilities JSONB DEFAULT '[]'::jsonb NOT NULL,
    approval_timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    session_id VARCHAR(36) NULL,
    action_type VARCHAR(100) DEFAULT 'general_approval',
    action_context JSONB DEFAULT '{}'::jsonb,
    org_id VARCHAR(36) NULL,
    branch_id VARCHAR(36) NULL,
    isDeleted BOOLEAN DEFAULT false,
    CONSTRAINT fk_manager_approvals_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_manager_approvals_auth FOREIGN KEY (auth_id) REFERENCES user_qr_auth(auth_id) ON DELETE CASCADE
);

-- Create indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_manager_approvals_user_id ON manager_approvals(user_id);
CREATE INDEX IF NOT EXISTS idx_manager_approvals_session_id ON manager_approvals(session_id);
CREATE INDEX IF NOT EXISTS idx_manager_approvals_timestamp ON manager_approvals(approval_timestamp);
CREATE INDEX IF NOT EXISTS idx_manager_approvals_action_type ON manager_approvals(action_type);
CREATE INDEX IF NOT EXISTS idx_manager_approvals_org_branch ON manager_approvals(org_id, branch_id);

-- Create GIN index for JSONB capabilities column for fast array operations
CREATE INDEX IF NOT EXISTS idx_manager_approvals_capabilities ON manager_approvals USING GIN (capabilities);

COMMENT ON TABLE manager_approvals IS 'Logs all manager/supervisor approvals with their capabilities from groups';
COMMENT ON COLUMN manager_approvals.approval_id IS 'UUIDv7 primary key for this approval record';
COMMENT ON COLUMN manager_approvals.user_id IS 'Manager/supervisor who approved (references users table)';
COMMENT ON COLUMN manager_approvals.auth_id IS 'QR auth record used for this approval';
COMMENT ON COLUMN manager_approvals.capabilities IS 'JSONB array of capabilities from user groups at time of approval (e.g., ["read", "write", "delete"])';
COMMENT ON COLUMN manager_approvals.approval_timestamp IS 'When the approval occurred';
COMMENT ON COLUMN manager_approvals.session_id IS 'Optional: POS session or other session identifier';
COMMENT ON COLUMN manager_approvals.action_type IS 'Type of action approved (e.g., discount, delete, refund, general_approval)';
COMMENT ON COLUMN manager_approvals.action_context IS 'Additional context about what was approved (JSON)';
COMMENT ON COLUMN manager_approvals.org_id IS 'Organization ID for multi-org filtering';
COMMENT ON COLUMN manager_approvals.branch_id IS 'Branch ID for multi-branch filtering';
COMMENT ON COLUMN manager_approvals.isDeleted IS 'Soft delete flag';
